0

Sorry if this is a noob question. I have transaction log shipping set up for a failover scenario. All the databases are in 'Restoring...' state until I do restore database x with recovery. At this time I disable log shipping.

Once I have resolved the situation and I want to enable log shipping and carry on again. Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out?

Improve this question
4
  • What was the situation you needed to resolve? Did you failover to secondary database or did you just bring it online to get some data that had been updated or deleted?
    – James Anderson
    Commented Jul 7, 2014 at 9:51
  • hypothetically the live server crashes and goes down. The failover is on a separate server with everything set up in IIS to run the websites/services from it. Due to log shipping all the databases are in 'Restoring...' state so I bring them all online and switch DNS over. Once the live server is repaired, I will switch DNS back. At this point I want to restart log shipping but the databases won't be in 'Restoring...' state anymore, so I wonder if I must set them back to that somehow.
    – NibblyPig
    Commented Jul 7, 2014 at 9:54
  • 1
    Is there any reason you cant keep the secondary DBs online and logship the other way until the next failure? This explains the setup to make failover easier in the future msdn.microsoft.com/en-us/library/ms178117.aspx
    – James Anderson
    Commented Jul 7, 2014 at 10:17
  • Nope no reason, just wasn't sure if it would work or not. If it's just a case of re-enabling log shipping and letting it execute then it's no problem. I wasn't sure if I'd get an error or other unexpected behaviour.
    – NibblyPig
    Commented Jul 7, 2014 at 10:34

3 Answers 3

Reset to default
1

It's not quite as simple as re-enabling log shipping as you need to do some initial setup to be able to switch from one instance to another.

This article helps with that

You also need to perform some clean-up before switching back to the original primary as it will be holding databases that are now out-of-date.

If your hardware on the primary and secondary are the same and there is no performance loss or maintenance headache in staying on the secondary server then I would recommend doing that until the next failure as the step of failing back is not needed.

Improve this answer
3
  • There's still data loss though between the time you failed over and the time the log was last shipped, the failover server runs in read-only mode because it's much easier than trying to sync back, unless there is a way to eliminate the gap
    – NibblyPig
    Commented Jul 7, 2014 at 12:43
  • 1
    depends on the nature of the failure. If the original primary server is still present you can do a log tail restore to bring the secondary up-to-date
    – James Anderson
    Commented Jul 7, 2014 at 13:01
  • Nice. I need to learn more about SQL to figure out how to do all these things.
    – NibblyPig
    Commented Jul 7, 2014 at 14:07
0

Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out?

you need to resetup log shipping by taking full backup from primary , copying it to secondary and restoring it on secondary with NORECOVERY. Then keep applying subsequent log backups taken from primary, copying them to secondary and restoring them on secondary (the log shipping wizard will help you with this)

Improve this answer
0

Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out?

The answer to this question is: it depends... The process that you go through to fail over to the Secondary will directly impact whether or not you need to refresh the Secondary from a full backup. A key question is whether or not you're able to connect to the Primary when bringing up the Secondary. Let's consider the process you mention above:

  1. Bring Secondary online.
  2. Point DNS at the Secondary so applications can connect.

If the Primary is still running, this means both Primary and Secondary are now in RECOVERY mode, which will end up breaking the Log Shipping process if the LSNs are advanced on both the Primary and Secondary.

Instead, to prepare the environment for failing back later you want to put the Primary databases into NORECOVERY mode before you fail over to the Secondary. The steps look like this:

  1. Back up the tail of the log for each database on the Primary, making sure to leave them in NORECOVERY mode.
  2. Apply the tail of the log for each database on the Secondary, bringing the LSNs in line with the Primary.
  3. Bring the Secondary online.
  4. Point DNS at the Secondary so applications can connect.

Then, should you then want to fail back to the Primary after it comes back online, you essentially reverse the steps:

  1. Back up the tail of the log for each database on the Secondary, making sure to leave them in NORECOVERY mode.
  2. Apply the tail of the log for each database on the Primary, bringing the LSNs back in line with the Secondary.
  3. Bring the Primary online.
  4. Point DNS at the Primary so applications can connect.

It's more involved than just the above steps, such as needing to stop the Log Shipping jobs when you fail over, enable them when you fail back, etc., but it helps with the main idea.

To make this an easier and more repeatable process, consider using this solution for a jump start. We use it regularly to test our DR procedures and ensure we can recover in a DR situation.

Improve this answer

Your Answer

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

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