1

Am log shipping a large, busy DB from server A (primary) to server B (secondary).

Nightly backups are taken, as well as diffs every X hours, and transaction logs are shipped and restored every 15 mins.

Occasionally I need to dry-run a script on server B, to assess it's impact, before running on server B. Script is often DDL.

I do this by taking server B out of RESTORING mode by manually executing a restore command on a recent transaction log, and testing the script.

However, to then resume log shipping, I have to restore the most recent full db, and diff, before log shipping can resume. This is very time consuming.

On server B, is there any way to instead effectively rollback to a previous transaction log (before I tried the script), and then resume log shipping?

Many thanks

0

1 Answer 1

4

On server B, is there any way to instead effectively rollback to a previous transaction log (before I tried the script), and then resume log shipping?

No there is no way to do this. You can only bring a log shipped secondary to standby mode. Which allow read_only operations. This is done by keeping an undo file. When you need to restore log files again contents from the undo files are applied first.

More details about undo file here:

In SQL server, How to see the standby file path when we restore the database in standby mode by Harsh Chawla.

For testing DDL you need to bring database to read_write mode which will undo uncommitted transactions without keeping a log of those pages. There is no way to revert to previous state as you can do in case of standby.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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