Log Shipping Fail-over

I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible. My Steps for fail-over will be

  1. Copy and restore T-logs (WITH RECOVERY) that are not applied, to put the database online
  2. Since the Primary in not accessible so I cannot take a T-log and put it in restoring state
  3. Disable the above two jobs
  4. Change the application connection strings to the new primary.

My confusion arises what should I do when the Old Primary comes live again:

  1. Should I take the T-log back up and put the database in restoring state so that I can set it up as a secondary and then fail-over to make it primary.

If that’s the case, then won't the log shipping fail if I want to configure it without initializing (since I have the database in restoring state) as it will look for the last T-log that I have not applied to my new secondary, as the primary was not accessible during fail-over?

1 Answer 1


The issues and complications you mention are ones I've wrestled with myself. Quite a while back, I did my best to script out a solution to help make it easier to fail over and also to fail back. I would suggest going to codeproject.com and searching for "Streamline Log Shipping Failovers" to look at the quick article I wrote there. You can download the SQL script and see if it's helpful for your situation. Essentially, it is a SQL script that you run on the Secondary and it dynamically generates the scripts you need to fail over and later fail back.

I have used it many times in a Production environment to perform DR tests. I have also used it once in a true Production down situation and it really helps streamline the process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.