Click here to Skip to main content
Click here to Skip to main content
Go to top

Streamline Log Shipping Failovers

, 20 Aug 2014
Rate this:
Please Sign up or sign in to vote.
SQL script that dynamically creates the DR steps for failing over/back all Log Shipped databases. Most helpful for multiple databases, such as SharePoint, consolidated SQL Servers, etc. However, it makes even a single database failover/back a more straightforward process.


Are any of the following true for you?  Would you like to...

  • Perform a DR test to demonstrate you are prepared for a disaster.
  • Bring up the Secondary because the Primary is down.
  • Migrate to a new server with minimal downtime. 

If so, let's get to it...

[Click here to sign up for notifications of code updates. All code updates will be made available on both sites.]

Getting Started…

  1. Unzip the file associated with this article and open SQL_DR_Master.sql in SQL Management Studio.
  2. Update the three variables found after the variable declarations.
    • @FailOverFromPRIMARY - controls whether to attempt to fail over from the PRIMARY or not. If set to 'Y', a final transaction log backup will be attempted on the PRIMARY before applying all available transaction logs. If set to 'N', the SECONDARY will not attempt the backup from the PRIMARY before applying all available logs.
    • @ScriptsLocation - sets the existing network location where the failover scripts will be created. For example, '\\MyServer\MyFailoverFolder', which should be a safe and accessible place.
    • @RunType - defaults to 'Automatic', which tells script 04.sql to apply transaction logs automatically and then show the statements that were run.
  3. Review the Considerations section within the script. For example:
    • Select 'Query -> Results To -> Results To Grid' in SQL Management Studio.
    • Ensure a LinkedServer from Secondary to Primary exists (if failing over).
    • Verify the variables mentioned up above are set correctly.
    • Etc.
  4. Execute the script against the SECONDARY. This does NOT actually begin the failover process. It simply generates the scripts that will be used to perform the failover.
    • Safe to run anytime on the SECONDARY server for review of scripts / steps produced.
    • If actually failing over, run as part of that process and not before as the generated scripts would be out of sync with the current environment.

Executing the Failover

Below is a table describing the main actions each generated script will take, along with the desired outcomes. The first 5 scripts fail over from the PRIMARY to the SECONDARY and the last 5 scripts fail back from the SECONDARY to the PRIMARY. Review the table below and take the time to go over the scripts it outputs to ensure things make sense to you and you don't foresee any issues. After confirming things look good with the scripts produced, you are ready to begin with the first step of failing over your server.

Script Target Server Primary Action Expected Outcome
01.sql PRIMARY Creates the directory that will hold the scripts produced, and disables LS backup jobs. New folder now available in the network location specified and LS Backup jobs disabled.
02.sql SECONDARY Removes the LS restore delay, applies all logs, and disables LS restore jobs. SECONDARY's databases have had all currently available logs applied.
03.sql PRIMARY Performs log backups. All databases are in NORECOVERY mode, waiting for failing back
04.sql SECONDARY Generates list of any remaining logs to apply to databases and restores them. All of SECONDARY's databases have had all logs applied. Databases remain inaccessible.
05.sql SECONDARY Executes restore command on each database. All of SECONDARY databases are available and ready for use.
06.sql SECONDARY Performs log backups. All databases are in NORECOVERY mode, waiting for LS to reengage
07.sql PRIMARY Restores available log backups. All of PRIMARY's databases have had all currently available logs applied. Databases have limited accessibility.
08.sql PRIMARY Sets all databases back to multi-user mode. All databases are fully accessible.
09.sql PRIMARY Enables all LS backup jobs. Log backups are being made again by the PRIMARY.
10.sql SECONDARY Enables all LS restore jobs. Log backups are being restored again by the SECONDARY and Log Shipping is running again.

To begin the Failover, open 01.sql in SQL Management Studio, verify it's connected to the correct server, and click Execute. Repeat this with 02.sql - 05.sql to fully complete the Failover. Refer to the table above as you go to help ensure things are going as expected.

After executing 05.sql, you will now be running on the SECONDARY server. If this is a DR test, you can perform some tests on the SECONDARY (for example, temporarily pointing your application at it to ensure it works properly). When finished, execute 06.sql through 10.sql to fail back to the PRIMARY (carries over any changes made on the SECONDARY). Make sure to repoint your application back to the PRIMARY and do whatever testing is necessary to make certain things are as expected and completely ready for your users.


Remember to create any additional documentation around the use of this solution, if needed. Also, don’t forget to periodically perform other steps that are needed for a proper failover as well (copy over logins and jobs, etc.).


This article, along with any associated source code and files, is licensed under The MIT License


About the Author

Micah Nikkel

United States United States
No Biography provided
Follow on   LinkedIn

Comments and Discussions

SuggestionUsing Log Shipping To Migrate To New Server PinmemberMicah Nikkel3-Sep-14 4:06 
Questionquery completed with errors and no scripts are generated PinmemberMember 110483991-Sep-14 7:01 
AnswerRe: query completed with errors and no scripts are generated PinmemberMicah Nikkel1-Sep-14 11:36 
GeneralRe: query completed with errors and no scripts are generated PinmemberMicah Nikkel3-Sep-14 4:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 20 Aug 2014
Article Copyright 2014 by Micah Nikkel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid