2

Looking for the fastest way of restoring multiple SQL Server standby databases that got out of sync.

  • Server A is a production server, has 50 databases configured with LSBACKUP jobs.

  • Server B is a standby server, has 50 databases in READONLY/STANDBY mode getting up to date with LSCOPY and LSRESTORE jobs.

The following error arises after a long maintenance*:

The log in this backup set begins at LSN 5286000000042100001, which is too recent to apply to the database. An earlier log backup that includes LSN 5286000000039400001 can be restored.

Obviously, the required LSN is nowhere to be found so the way to fix this would be to take a full backup on production database with NORECOVERY and restore it on the standby, then restart the restoring jobs.

The issue here is doing so one by one is a long and tedious process:

What is the fastest approach to solve this problem? I'm not as much looking for a way to make the backups faster than a way to automate the whole.

* We downgraded from Enterprise to Standard and somehow the log shipping chain got broken in the process.

Improve this question
0

2 Answers 2

Reset to default
4

Below is the approach that I would take :

  • Stop logshipping jobs on both primary and secondary database servers.
  • If a FULL Backup job is run on the primary server then you have to initialize logshipping by restoring the latest full backup and a diff or subsequent log backups.

  • If a full NON COPY ONLY backup is not run on primary server, you can follow the rest

    • Take differential backups of the databases on primary server.
    • Move all the backups to the secondary database server. You can use Robocopy or powershell to do this.
    • Restore the DIFFS on the secondary server using norecovery.
    • start logshipping jobs on primary and secondary.
    • At this point your logshipping should be resumed.
Improve this answer
2
  • I did this and it worked, yet it gets quite repetitive and long when you have many databases to do that on. That's mostly why I was asking if there's a way to automate or shorten the process you just described.
    – A_V
    Oct 19, 2016 at 12:16
  • 2
    If you script out using dynamic sql or powershell, its very easy. I have done it for 300+ databases and it works fine.
    – Kin Shah
    Oct 19, 2016 at 13:04
0

This can be done with a TSQL Script with a cursor (a loop) and a VB script called from within the SQL Script.

An excellent example with full source code is available here: https://www.mssqltips.com/sqlservertip/1516/automate-restoration-of-log-shipping-databases-for-failover-in-sql-server/

Improve this answer

Your Answer

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

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