Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Does anyone know if there's a T-SQL way to place a SQL Server database into standby read-only mode like you can with the STANDBY clause of BACKUP DATABASE, only without doing a BACKUP DATABASE?

Background: I have a home-made log shipping setup on SQL Server 2005 Workgroup Edition. I've found a 3rd party backup utility which does compressed backups. It's faster than BACKUP DATABASE and the files are a lot smaller but it lacks the ability to place a database into standby/read-only mode. I need this capability for failing over when doing disaster recovery testing. If we fail over and log ship from our (now active) standby server back to our primary server we can simply fail back again at the end of testing instead of starting all over again. I can do the log tail backup with BACKUP DATABASE instead of the 3rd party tool but it would be cleaner if I didn't have to.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

No there isn't a way to put a database into stand-by mode without restoring the database from a backup at the same time. Once the database is writeable you can't make it ready to restore logs again as the LSN chain has been broken.

share|improve this answer
add comment

Could you use

ALTER DATABASE your_database SET READ_ONLY

then use

ALTER DATABASE your_database SET READ_WRITE

when you want to revert?

share|improve this answer
    
Thanks, Marko. That covers the read-only bit but the standby mode seems to be the problem. –  David Wimbush Jul 10 '09 at 15:58
add comment

I've got to admit, I replied to your other post (about your backup software) and now that I'm reading this question I'm actually a bit concerned about the backup software you're using.

This is one of those things where "too complex" of a solution might actually lead to disaster down the road. I don't know anything about the backup software you're using but a quick Google showed several poor reviews (but unfortunately outdated reviews).

So, I don't know. Is there any particular reason why you want to use this backup software instead of trusting MS SQL Server to do it for you?

Surely speed and disk space can't be THAT much more important that DATA INTEGRITY and RELIABILITY?

Am I missing something here?

Maybe Paul Randal might want to chime in here?

share|improve this answer
    
There are several 3rd party backup compression tools available. When Microsoft added it to 2008 I presume they were reacting to a clear customer demand for this feature. I'd love to upgrade to 2008 and use native backup compression but, as it's Enterprise only, expense rules that out. Paul Randal has (in his own words) 'gone dark' for July but he has certainly been impressed with 2008's backup compression. I don't think this is that risky and, yes, speed is a factor. I have to choose between speeding up the backup/restore time or completely redesigning my data warehouse solution. –  David Wimbush Jul 14 '09 at 7:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.