Fastest way of restoring log shipping of multiple databases 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.
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.