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.
Best Answer
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.