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 withLSCOPY
andLSRESTORE
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.