Quickest way to resynch logshipping

I look after a SQL Server with around 20 databases on it that are shipped to another server with no witness. A few times now I have had to resynch a database and just done it manually through the SSMS interface.

Recently however I had to resynch all twenty and wanted to find the quickest way without having to reconfigure all the databases.

Firstly I went through and created a backup for the first database, deleted all the old transaction log backups as figured they would cause the restore job to fail as being out of sequence. Restored the backup to secondary with no recovery and kicked things off again. All worked fine and back in synch.

However that was a bit too much extra work, deleting all the old transaction logs so next I tried just backing up and restoring, to see what would happen. Too my surprise the restore job did not fail on attempting the previous transaction logs, it appeared to just ignore them. Seamlessly picking up from where we left off the server was back in action. (SQL 2008 R2)

So in summary the quickest way to resynch logshipping is simply to do the below. Leaving all else in place.

On the primary server

Backup database to a shared location.

On the secondary server
Restore database with No Recovery.

As simple as that. Then check everything synchs up with the custom report inside ssms.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.