SQL Server Log Shipping – Rapid Restart after Planned Failover

I was chatting with a group of DBAs the other day when the subject of SQL Server Log Shipping came up.  The consensus between them was that Log Shipping is a great, cheap feature but that the time it took to get it restarted after a failover was a huge pain.

“What do you mean?” I said.  “Before you perform the failover, you take a tail-log backup, then you can just restore the logs from the failover site back to the primary site when you’re done.”  You would think I had grown a third arm directly out of the top of my head.  This group, which included former Data Platform MVPs, told me I was crazy, that this would never work.

So, I demonstrated it to them and blew everyone’s mind. Now I’d like to share the process with you…

Set Up Log Shipping

I’ll start with a log shipped database (RunningOnEmpty) on my primary instance shipping to my secondary instance.

I’ll create some tables in RunningOnEmpty so it’s no longer just an empty database:

CREATE TABLE GAS_TANK (Gallons int)
CREATE TABLE CHECKING_ACCOUNT (USDollars decimal(10,2))
CREATE TABLE MA_BELLY (Cupcakes int)

Insert into GAS_TANK VALUES (0)
Insert into CHECKING_ACCOUNT VALUES (0)
Insert into MA_BELLY VALUES (0)

And then query them to see the results:

Planned Failover

The time has come to fail from the primary database to the log-shipped copy, so I’ll begin by taking a tail-log backup of the primary database?  I do this for two reasons:

• After this backup is done, it ensures there can be no additional write activity on the primary database because it is left in a RESTORING state and unable to accept connections.
• By placing it in the RESTORING state, I make it available to apply any subsequent transaction logs that are created on the DR copy.
  • Remember, the DR copy is literally a 100% copy of the primary database, meaning once I bring the DR copy out of recovery, it acts just as if it really is the primary database.  The log chain remains unbroken.

BACKUP LOG RunningOnEmpty TO DISK = 'D:\SQLBackup\RunningOnEmpty_TailLog.trn' WITH NORECOVERY

Once I run this command, you can see my primary database is now in the RESTORING state:

Now I can manually apply that backup to our DR instance, using the WITH RECOVERY option to make it available for use.  Log Shipping won’t handle log backups taken outside of its process, hence the need to manually apply it.

RESTORE LOG RunningOnEmpty FROM DISK = 'D:\SQLBackup\RunningOnEmpty_TailLog.trn' WITH RECOVERY

And the DR instance is ready to roll with my recovered database:

I’ll update some data so we can see some changes.

USE RunningOnEmpty;
GO
UPDATE GAS_TANK SET Gallons = 5
UPDATE CHECKING_ACCOUNT SET USDollars = 42.00
UPDATE MA_BELLY SET Cupcakes = 3

Great!  After a bit, I seem to be in a better place and it’s time to fail back to our primary site.  According to my experienced friends, this would be a very involved process with lots of new backups and copying large files across a WAN.  Let’s try an easier way, shall we?

I’ve been taking transaction log backups at the DR site, because it’s just like production.  For this demo, I’m just going to use one log backup, but in the real world I would need to restore all of the log backups taken at the DR site back to the primary site.

Fail Back

To begin the failback process, I take one final log backup at the DR site, just like when I started the failover process:

BACKUP LOG RunningOnEmpty TO DISK = 'D:\SQLBackup\TAILORSWIFT_RunningOnEmpty_TailLog.trn' WITH NORECOVERY

My DR instance is now back in RESTORING mode which is going to come in very handy later.

I’ll restore that backup back to my primary instance and check to see if my changes made it back:

RESTORE LOG RunningOnEmpty FROM DISK = 'D:\SQLBackup\TAILORSWIFT_RunningOnEmpty_TailLog.trn' WITH RECOVERY
GO
USE RunningOnEmpty
GO
SELECT * FROM GAS_TANK
SELECT * FROM CHECKING_ACCOUNT
SELECT * FROM MA_BELLY

Cool, my data is all there.  Now, what do I need to do to get log shipping working again?

ABSOLUTELY NOTHING.

The backup job that was running on the primary started to fail when I ran the final tail-log backup there because the database became unavailable.  Now that the database is back online, the backups are successful again.  If I was down for an extended period of time, I would have disabled this job and the restore job, so I would need to re-enable them.

I don’t have a copy job because for this demo both instances are on the same machine, so instead I look at the restore job on the DR instance.  It was also failing once we brought the DR copy out of recovery and began using it, but once it is back in the RESTORING state it will scan the folder with our backups and apply them as the backup process on the primary generates them again. That’s it!  Super-quick recovery from a failover of Log Shipping.  I was surprised that even some very experienced folks had not done it this way, so I wanted to share my experience more widely.