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?

  • Are you using built-in log shipping functionality, or custom backup/restore scripts? – db2 Dec 27 '13 at 15:31
  • Custom backup/restore scripts. – user32161 Dec 27 '13 at 15:32
  • Just take the backups being sent to the FTP (or something) and use those to restore to new database. Most setups I have seen will send over a full backup at some point to restore. – user507 Dec 27 '13 at 15:55

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.

  ON (NAME = 'datafile', FILENAME = 'F:\TargetDB_Read.SNP')

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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