Upgrading SQL Server–Migrating with Log Shipping

One easy and quick way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use log shipping. Log shipping may not be new and shiny like availability groups, but it has very few blockers for implementation, and it does not add any additional workload to your legacy databases after you get it running. You do have to have each database in Full or bulk-logged recovery model before you can use log shipping. If you have a database in simple recovery model, and you try to enable log shipping, you will get a message like what you see in Figure 1.


Figure 1: Log Shipping Error Message


Log shipping does not have automatic fail over out of the the box, but you can pretty easily write some T-SQL to run the proper SQL Server Agent jobs on the primary and secondary server, check the status of the secondary database, and then recover the secondary database and bring it online on the new server. Doing all of this can semi-automate your fail over process.

Here are the basic steps for doing the final Production migration:

  1. Stop the application(s) that are using the primary database(s) on the primary server
  2. Run the LSBackup Agent job on the primary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSBackup_AdventureWorks2014′;
  3. Run the LSCopy Agent job on the secondary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSCopy_LABDB03_AdventureWorks2014′;
  4. Run the LSRestore Agent job on the secondary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSRestore_LABDB03_AdventureWorks2014′;
  5. Check the recent history of the log shipped databases on the secondary server
    1. Query the log_shipping_monitor_history_detail table in msdb
  6. Restore each log shipped database on the secondary WITH RECOVERY
  7. Redirect the applications to the new database server

Steps two through six can usually be done in just a few minutes, depending on how many databases you have, how much transaction log activity has occurred, and your network and storage infrastructure performance.


Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.


Here is a link to the complete series about upgrading SQL Server.

4 thoughts on “Upgrading SQL Server–Migrating with Log Shipping

  1. I listened to the whole course on log shipping. Very informative. I had one question and I did not find a clear answer yet when I googled it. The log file we have on the primary server is close to half terabyte and we copy the logs every 15 minutes to the secondary server. The question I have is this: Can I shrink the log file on the primary server by using the shrinkfile command? Will it affect negatively the logs that are being copied to the secondary server? The copied log files that are copied every 15 minutes are less than 100 MB in size. The log file size for the database on the secondary is also close to half terabyte. Please advise.

  2. Thanks for the kind words!

    You aren’t actually copying log files. Instead you are copying and restoring transaction log backup files. This means that any DDL commands (such as shrinking a log file) will be captured in the transaction log backup, and then they will be replayed on the secondary server database when that transaction log backup is restored.

    This means you can shrink the log file on the Primary and it will eventually shrink to the same size on the Secondary.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.