-
Title
Steps to resync Log Shipping on one subscriber if multiple subscribers are present -
Description
Steps to resync just onesubscriber if a plan has a publisher is shipping to multiple subscribers
-
Resolution
Procedure to resynchronize a secondary subscriber with log shipping
1. On the subscriber go to the dbo.LSPlan table in the LiteSpeedLocal database and get the PlanID for the subscriber that is no longer in sync.
2. On the subscriber remove the entries for this PlanID in the following tables:
a. LSRestoreLog
b. LSBackupLog
3. Select the last full backup of the database that is out of sync and note the location.
4. Disable all LogShipping Jobs. At the very least, the delete and copy job have to be disabled.
5. Run the following script on the publisher - replace LIteSpeedDemo with the name of the database
select
s1.type,
s1.backup_start_date,
s1.backup_finish_date,
s1.first_lsn,
s1.last_lsn,s1.checkpoint_lsn,
s1.database_backup_lsn,
s2.physical_device_name
from msdb..backupset s1 inner join msdb..backupmediafamily s2
on s1.media_set_id = s2.media_set_id
where s1.database_name =LiteSpeedDemo
and s1.type in(D,L,I) -- sl.type in (D,L) means full or Transaction Log backups (if I, Differential backups)
and s1.backup_start_date >= (select max(backup_start_date) from msdb..backupset where database_name =LiteSpeedDemo and type =D)
order by s1.backup_start_date asc
6. Run the following script on the subscriber
SELECT s1.restore_type
, s2.first_lsn
, s2.last_lsn
, s2.checkpoint_lsn
, s2.database_backup_lsn
, s1.restore_date
, s2.backup_start_date
, s1.destination_database_name
, s1.backup_set_id
, s3.physical_device_name
FROM msdb..restorehistory as s1 INNER JOIN msdb..backupset as s2
ON s1.backup_set_id = s2.backup_set_id
INNER JOIN msdb..backupmediafamily as s3
ON s2.media_set_id = s3.media_set_id
where
s1.destination_database_name =LiteSpeedDemo_R -- the database restored
and s1.restore_type in(D,L) -- sl.type in (D,L) means full or Transaction Log backups
and s1.restore_date >= (select max(backup_start_date) from msdb..backupset where database_name =LiteSpeedDemo and type =D)
order by s1.restore_date asc
7. Compare the results of the two to ascertain the transaction logs that will need to be added to the subscribing database.
8. Open LiteSpeed Enterprise Consol - Select Backup from the menu and then Restore Database Wizard. Select the Server and the Database that is out of sync. Click NEXT
9. In the section where you specify the files to be restored, select the full database restore as the first file and then list the transaction logs that need to be restored after. Make sure that the IntelliRestore feature is unchecked.
10. In the next page make sure that all the files are checked that you want to restore and that the IntelliRestore box is unchecked again.
11. In the Recovery Options Page make sure that you select the third option under Recovery Completion State-Leave database read-only and able to restore additional transaction logs and an undo file is specified.
12. In the Data Files page make sure that the Force Restore over Existing Database box is checked if the unsynchronized database is still in place.
13. Continue clicking on Next until the wizard is complete and then run the restore.
14. As soon as the restore has completed successfully, re-enable the two previously disabled jobs on their respective servers.
15. When the jobs run now the databases will stay in sync. -
Additional Information
This document is good for Subscriber out of sync for a long time where t-logs have already cleaned up from the backup and share location.
If they were not, the customer just need to compare the LSBackupLog table from the publisher to the subscriber server to see if there are any records missing and added it back to the subscriber server LSBackupLog table. Stopping jobs would be required.