Migrating Log Shipping Database

Log Shipping is one of the HADR solution available in SQL Server and it works in a simple way – take log backup on the primary database and restore it on the secondary database. There can be more than one secondary databases. All this can be scheduled using SQL server agent jobs and even if the feature is not available is directly, it should be fairly easy to set one up in a traditional way.

I recently had to move one of our log shipping primary database to a different database server. Below are the steps, I followed the below steps to eliminate the need to reconfigure the log shipping from scratch.

Scenario :
Database Server : Mercury – Primary Log shipping ;
Database Server : Venus – Secondary Log shipping
There is no witness server configured.

Now, I need to migrate ‘TestLogship’ database to new database server ‘EARTH’ and configure it as log shipping primary.

1.Take full backup on the mercury server for ‘testlogship ‘ database.

2.Restore the full backup on the Earth database server in recovering mode.

3.Run select * from msdb.dbo.restorehistory where destination_database_name=’testlogship’ on the venus database server, to find which was the last log backup applied.

4.Make sure none of the log shipping jobs(backup\copy\restore) are running and disable them.

5.If there are any log backups taken but not applied on to Venus database server – apply them. This can be tracked by checking backupset table on Mercury and comparing it with restorehistory table on Venus.

6.Check if there are any log backups taken after the full backup(step1) was taken and restore them on database server Earth in ‘recovering’ mode.

7. Make sure both the Earth and Venus, database servers have same log backups applied. You can verify this by checking ‘select * from msdb.dbo.restorehistory where destination_database_name=’testlogship”.

8.Restore the database on earth with recovery.

9. Right click on the database ‘testlogship’ on the EARTH server and –tasks — ship transaction logs and configure log shipping, like you normal would but choose ‘secondary database is initialized option’ and everything else would obvious, if you have configured log shipping before.

NOTE: One easy way to do this is instead of restoring all the log backups, you can stop the log shipping jobs and take differential backup on the primary ‘Mercury’ and restore the differential backups on both ‘Venus and ‘Earth’ and then continue from step 8 above.