Copying a restoring database in SQL Server 2012

Question

I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I simply have to trust that it is doing its job of periodically uploading its transaction logs to Server B (via something like FTP).

Now, suppose Server B needs to recover the database to do some reading and writing. Doing this will break its ability to receive further transaction log backups.

Is there any way to copy/branch/backup the restoring database, so I can have one version of it that will continue to apply the transaction logs, and one version that will be recovered for reading/writing?

Answer 1

If you want to (1) maintain your log shipping environment and (2) want to have a read/write copy occassionally you will need to maintain 2 databases, TargetDB and Target2DB.

Question, could you just restore the backup from the Server A SourceDB to Server B Target2DB? That might take a little longer, but there are fewer moving parts running all the time.

If read/only access would be sufficient, you can create a database snapshot for reading purposes. This creates a point in time snapshot of the database, supported by the .SNP file that maintains the needed data to support the snapshot, despite other changes coming in.

CREATE DATABASE TargetDB_Read
  ON (NAME = 'datafile', FILENAME = 'F:\TargetDB_Read.SNP')
  AS SNAPSHOT OF TargetDB

Note: You can create several snapshots if needed at different points in time. In that case, name the snapshot for the time it is created. E.g. TargetDB_0800.) See: http://technet.microsoft.com/en-us/library/ms175876.aspx

Of course if you bring Target2DB online so as to do Read/Write, once you are done you will need to restore a Full backup of the SourceDB as the new Target2DB, then catch up with changes through restoring Differential and Log backups until you are once again ready for log shipping.

Answer 2

It won't be possible to write to the database on server B if you want to restore new transaction logs from server A to it. However it is possible to restore the transaction logs to the database on server B in standby/readonly modus . Then you can read from the restored database.

Example how to restore a transaction log and set the database in standby-readonly mode:

RESTORE LOG [Test2] FROM  DISK = N'C:\MssqlDatabases_2012\Backup\Test1latest.bak' WITH  FILE = 1,  
STANDBY = N'C:\MssqlDatabases_2012\Backup\Test1_RollbackUndo_2013-12-27_16-42-05.bak',  NOUNLOAD,  STATS = 5