Reinitializing Log Shipping Subscriptions
If the subscriber gets out of sync and the log shipping subscription needs to be restarted with a full backup:,
1.Disable the log shipping jobs.On both the publisher and the subscriber, go into Management Studio or Enterprise Manager and find the SQL Agent log shipping jobs for the plan that needs to be reinitialized. Right-click on each job and click Disable. If this is not done, theres a chance that extra transaction logs will be backed up, but wont be timed correctly and will disrupt our efforts to reinitialize the plan.
2.Get the PlanID.Go into the publishers LiteSpeedLocal database and run this query to list the plans:
SELECT * FROM dbo.LogShippingPlan
Find the plan that needs to be reinitialized and copy the PlanID field contents.
3.Take a full backup on the publisher.In the publishers LiteSpeedLocal database, run this query to take another full backup:
exec dbo.LogShipping_Update_PrimaryDatabase @PlanID=653DBF10-3E42-46E0-8BB2-AF8E12FF6A47, @PerformBackup=1
In that example, replace the @PlanID example with the PlanID that needs to be reinitialized, taken from step #2.
4.Copy the backup file to the subscriber.Copy the newly created backup file to the subscribers distribution folder manually. The backup file will be in the publishers distribution folder and will be named with the database name only no date or extra file name characters. Dont just run the file-copy job on the subscriber, because that wont copy full backups.
5.Restore the full backup on the subscriber.Go into the subscribers LiteSpeedLocal database and run this query to start the restore:
exec dbo.LogShipping_Sync_Subscriber @PlanID=653DBF10-3E42-46E0-8BB2-AF8E12FF6A47
Make sure the restore completes successfully.
6.Enable the log shipping jobs.Go back into the jobs that were disabled in step 1 and enable them again. Test them by running them in this order: the publishers backup job, the subscribers file-copy job and then the subscribers restore job. If they all complete successfully, log shipping has been reinitialized.