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

Improve this question
4
  • Use WITH STANDBY = 'c:\blah\MyStandByFileLocation.utf' docs.microsoft.com/en-us/sql/t-sql/statements/…
    – Sean Gallardy - Retired User
    Jan 21 '18 at 15:56
  • 1
    Can you expand how putting the DB into a read-only mode meets my needs? Thanks.
    – Nik
    Jan 21 '18 at 18:56
  • Set the database to standy, making sure you're at a point your script will have the required objects and data. If it is... Create a new database shell, offline the original database and the new database. Delete the new database files, copy of the old. Online the database. The standby is to make sure all items are there through LS...
    – Sean Gallardy - Retired User
    Jan 22 '18 at 14:04
  • @SeanGallardy-Microsoft you clearly know what you're talking about, but I can't quite follow these terse instructions. What is a database "shell" to start? thanks.
    – Nik
    Jan 23 '18 at 12:30
2

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.

Improve this answer

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.