Let’s say you’re a DBA managing a 2TB database. You use SQL Server transaction log shipping to keep a standby copy of the database nice and warm in case of emergency. Lots of data can change in your log shipping primary database: sometimes it’s index maintenance, sometimes it’s a code release, sometimes it’s just natural data processing.
And when a lot of data changes, your warm standby sometimes is a lot less warm than you’d like. It can take a long time to restore all those log files!
Here’s a trick that you can use to help “catch up” your secondary faster. A quick shout-out to my old friend Gina Jen, the SQL Server DBA and log shipper extra-ordinaire who taught me this cool trick years ago in a land far far away.
Log shipping Secret Weapon: Differential Backups
Through lots of testing and wily engineering, you’ve managed to configure nightly compressed full backups for your 2TB database that are pretty darn fast. (No, not everyone backs up this much data every night, but stick with me for the sake of the example.)
- Log shipping primary had a full backup last night at 2 am
- Log shipping secondary has transaction logs restored through 7 am
- It’s 3 pm, and you’d really like to have everything caught up before you leave the office
Here’s an option: run a compressed differential backup against your log shipping primary. Leave all the log shipping backup and copy jobs running, though — you don’t need to expose yourself to the potential of data loss.
After the differential backup finishes, copy it over to a nice fast place to restore to your secondary server. Disable the log shipping restore job for that database, and restore the differential backup with NORECOVERY. This will effectively catch you up, and then you can re-enable the log shipping restore and you’re off to the races!
But Wait A Second. Aren’t Those Full Backups A Problem?
Running a full backup without the COPY_ONLY keyword will reset the “differential base”. That means that each differential backup contains changes since the last full backup.
But here’s the cool thing about log shipping: restoring a transaction log brings the new differential base over to the secondary.
So as long as you’ve restored transaction logs past the point of the prior full backup, you can restore a differential to your log shipping secondary.
This Sounds Too Good To Be True. What Can Go Wrong?
This isn’t foolproof. If you haven’t run a full backup in a long time, your differential backup may be really big, and taking that backup and restoring it may take much longer than restoring the logs. (Even if you’re using log shipping, you should be doing regular full backups, by the way.)
And like I mentioned above, if your log restores are so far behind that they haven’t “caught up” with the last full backup taken on the primary, you’re not going to be able to restore that differential backup to the secondary.
What If a Transaction Log Backup File Is Missing?
A technique like this could work for you, as long as a full backup hasn’t run since the transaction log backup file went missing. (If it has, you need to re-setup log shipping using a full).
But a word of warning: if you have missing transaction log backup files, you have a “broken” log chain. You should take a full backup of your log shipping primary database to get you to a point where you have functioning log backups after it, even if you’re going to use a differential to bridge the gap on your log shipping secondary. (And keep that differential around, too!) Keep in mind that you won’t have point-in-time recovery for a period around where the log file is missing, too.
Log shipping is Great
I just love log shipping. It’s quick to set up, it’s relatively easy to manage, it’s included in Standard Edition, and it’s got these surprising little tricks that make it easy to keep going. You can learn more about log shipping from Jes, and join us in person in 2015 in our Senior DBA training, which includes an advanced module on log shipping.