SQL Server – How to Change Database Mode to STANDBY

recoverysql server

I have a "log shipping" from server A(Database Name = main) to server B(Database Name = LS_main) .
I want to take a Snapshot Database From the secondary Database (Database Name = LS_main), but he id in STANDBY mode (Read_only) and I can't do it.

How can I do it ?

(I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode…is it right to do ?)

Best Answer

I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?

What you are telling is not possible using logshipping.

There are limitations of database snapshots:

In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.

There are some options:

If your database is not that big, you can do a snapshot replication, if you really want a snapshot of your database as opposed to doing a backup/restore.
If you want a near-to-realtime data on some secondary server, then transactional replication would be a better option.
Alternatively, you can use database mirroring. It allows database snapshot on the mirrored server but the database must be in the synchronized mirroring state.
Now that SQL Server 2012 and 2014 are out with AlwaysON, you can consider that as well if you want a read-only copy of your database.

Note that database snapshots as well as AlwaysON are Enterprise features :-)

Related Solutions

SQL Server – Can Full Backup Be Taken on Log-Shipping Secondary Database?

I believe you cannot take a native backup of the databases if they are in Standby/Read Only mode.

So backing up the secondary database would be not be a good go.

Also, if LS DB is in "Read only mode" you can run DBCC CHECKDB , it will complete but it would not get into the boot page and hence not the check you expected it to be. Moreover even if it reports or finds an error you would not be able to run a repair statement with DBCC CHECK.

What you can do is, take backup of the DB1 during off peak hours over a network to another test/dev machine with appropriate resources to restore the backup.

From the restored backup, you can perform the DBCC CHECKDB on the restored database.

Also, please read this excellent article by Aaron Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts for best practises on it.

SQL Server 2008 – Recover Database from Standby/Read-Only Without Standby File

Turns out it can be done if you use CONTINUE_AFTER_ERROR

RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR

I did still get a warning when I tried it but then did a CHECKDB and received no errors.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 0 pages in 4.180 seconds (0.000 MB/sec).
Msg 3441, Level 17, State 1, Line 13
During startup of warm standby database 'foo' (database ID 46), its standby file ('C:\MSSQL\Backup\foo_standby') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

Given the warning I'm not 100% certain if I would try it on a production database where I didn't have a good backup. Given that it's a temp restore it might be worth giving it a shot.

Best Answer

Related Solutions

SQL Server – Can Full Backup Be Taken on Log-Shipping Secondary Database?

SQL Server 2008 – Recover Database from Standby/Read-Only Without Standby File

Related Question