How to take Snapshot of Restoring Database

Question

I have set up log shipping with my database to make a backup. The backup copy of the database is in a status called '(Restoring...)'.

I understand this is to be expected? Is there a good way I can tell whether the log shipping is actually working?

I have run the stored procedures sp_help_log_shipping_monitor_primary, and sp_help_log_shipping_monitor_secondary which are all good.

What I had in mind was trying to understand what the restoring mode is. If there is a way of backing up and restoring that database so I can take a separate copy of it to check?

In an emergency, how do you take it out of the restoring mode?

Answer 1

Its a bit round about. But first you have the option in the configuration to change the resulting database to 'standby mode'.

This results in a destination database which is grey, and is marked as 'Standby / Read-Only'. You can look at the tables and query it etc.

Take the database offline (TestReplication is what mine is called), and copy the mdf and ldf files. Put it back online again.

Create a new database called 'TestReplicationNew'. Take it offline, and copy the mdf and ldf files from the last step over top of the TestReplicationNew mdl and ldf files.

Take it back online and you have a read write database that you can test works.

If I don't do it like this I get an error,

cannot attach a database that was being restored