Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary
by Kendra Little

from http://www.brentozar.com/archive/2014/09/log-shipping-magic-using-differential-backup-delayed-secondary/

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.