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.
30 Comments. Leave new
A colleague tells me this also works with catching up broken secondaries in database mirroring, but I’ve never tried it.
I’ve had this working with database mirroring (e.g. breaking the mirror, carrying out extensive re-indexing, then taking a diff of the principal and applying it to the mirror with NORECOVERY, then re-initialising mirroring with a log backup \ restore with NORECOVERY.
I’ve also had this exact same sequence completely fail on me too because the diff flatly refuses to be restored to the mirror.
I never got to the bottom of why this worked sometimes and not others, and it’s always bugged me. Hmmmm.
I guess logically you should be able to do this with Availability Groups too. I haven’t tried that yet though.
It has to be caught up enough that the differential bases match in order to be used. (In other words, my guess is that a full backup reset the differential base at some point when the mirror was already behind. Just a guess, though.)
With database mirroring, breaking the mirroring has tended to be a big deal, so I’ve typically reduced index maintenance as much as I can, and have also added a delay in between steps to keep the mirror from getting overwhelmed. Less sweaty palms!
What rolls down stairs, alone or in pairs…?
Hahaha, it’s a logshipping slinky!
that was a quick reply…only in one minute 🙂
Excellent post. It’s great to see experts like yourself continuing to recognize the value Log Shipping has to offer. You definitely taught me some things about it I didn’t know before, so thank you!
Hi Kendra,
Can this also be resolved by doing Log Shipping more frequently? Like:
1) Full Backup – Once a week(e.g. Sunday at 1am)
2) Differential Backup – Once a day(e.g. at 2am)
3) T-Logging – Every 5mins or 10mins.
In any case, the thank you for the informative article. Yes, Log shipping is great.
Thank you,
Lester
Thanks, Lester!
Even with really frequent log backups, the restores can take a while to crunch through if you’ve got a lot of data flowing through.
I would still set the timing of the log backups on the primary server based on your RPO and RTO — which might mean that they’re running every one minute, even! (Here’s a post by Brent on that: https://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/)
Kendra
Nice article, Kendra. One thing i really miss about Log Shipping is the lack of customization in the BACKUP/RESTORE syntaxes. Wish i could use all those MAXTRANSFERSIZE, BUFFERCOUNTs and BLOCKSIZEs.
Do you know any alternative to change the LS default BACKUP/RESTORE syntax?
You could write your own process. I did this a few years ago and it worked just fine. However, the end result really isn’t much more than creating something that already exists. I’m a big fan of log shipping because it is free, effective and very forgiving when it comes to things like network burps, etc.
I did that once when i had to set up a Log Shipping between Express instances.
Actually, it might be the only way since Microsoft will never touch on Log Shipping features again 😀
You can totally roll your own. My only warning is to not try to do a hybrid where you use the logshipping built in copy and restore and monitoring jobs and just try to write your own backup jobs. Logshipping is super picky about the filenames and datestamps in them, and troubleshooting weird issues that creep up can drive you nuts.
If you roll your own, also don’t forget to write your own monitoring 🙂
You can roll your own (as these other fine folks mention), or you could buy a third party tool to help. If you’re logshipping a lot of databases or just want help setting it up quickly and monitoring it, this can totally be worth it, plus it offers these extra features.
Dell’s Litespeed is a nice option for this- this page includes the parameters you can use: http://documents.software.dell.com/doc211503#LogShipping_Add_PrimaryDatabase
Kenda/James, thanks for the replies.
I did just as Kendra suggested. I covered everything right down to the monitoring report/alerts/clean up and it was a complete copy/replacement for the out of the box functionality. While I was impressed with myself, I did see it as really just reinventing the wheel, or answering a question that wasn’t asked.
One thing I did get from this was very unexpected. From time to time we have had an occasion where some data turns up missing. Nobody will admit to doing anything (strange, I know) and that means it’s time to do a point-in-time restore in a staging area. I end up having a lot of issues using the GUI version of this. There is no break in the full backup to the latest log backup. My restore stored procedure on the other hand gives me no trouble at all. My procedure to roll forward is my go to tool for this situation.
Can you restore a differential backup without restoring the full backup? What’s needed? Full recovery model and the database in the restoring state?
You can only use a differential backup with the full backup that set the differential base– so not just ANY full backup. So if that full is missing or doesn’t restore properly, the differential is useless.
Differential doesn’t require full recovery model.
I don’t understand what does “restoring a transaction log brings the new differential base over to the secondary” gain you if you are restoring from full+differential anyway
I don’t understand what does “restoring a transaction log brings the new differential base over to the secondary” gain you if you are restoring from full+differential anyway.
Ah, in the example in this post, the differential is being applied to the logshipping secondary. It’s been refreshed with transaction logs and perhaps was set up long ago.
I should have been more complete in my last answer: the differential backup can only be used with the full backup, or in a scenario like in the post (where something has the same differential base because it’s been refreshed by logshipping). Hope that helps!
I think I understand now, thank you. SQLServer lets you restore just the differential because all changes since the last full restore already present in the secondary are also in the differential. If log shipping catches up with the primary before you get around to restore the differential and the secondary is now more advanced than the differential the restore would fail, right?
Yep, SQL server can look at the log sequence numbers and figure out if the differential is too old / not fresh enough as well.
Kendra,
You mention log files going MIA so to speak. I’m curious if you have a theory as to why this happens? I used to think this occured during the copy of a large transaction file but even after reducing the primary backup interval to two minutes the gremlins struck again. I’ve since changed the primary backup to run every minute and so far so good but still have no clue why this happens.
Mike.
Mike – when it happens to me, it’s usually because somebody deleted one of the log files, or because a job was set up to delete the log files but the secondary hadn’t restored all of them yet.
Good afternoon everyone, I have a log shipping project giving me fits. Lets say I have servers on the west coast which are my PrimaryDB servers. My secondary servers are on the east coast. Backups take 15 mins but copying the full backup takes a little over and hour. Servers on east coast run east coast time, Servers on west coast run west coast time.
So to start with we ran the wizard and created the jobs but due to the 1 hour copy to the secondary, we manually backed up full, with NOINIT to disk. Copied the Backup file to the Secondary server. Restored the database into a standby state onto the new server. Then backed up the log from primary WITH INIT, copied to primary server, restored log to warm SecondaryDB.
Activated primary trn backup job, activated secondary copy job, activated secondary restore job.
Now the problem is 2 fold. The time differential means my LSAlert jobs all fail within 1 hour says nothing is being restored. All the *.trn files are skipped saying “Could not find a log backup that could be applied to the secondary database”
The main problem with the LSAlert jobs is they complain the SecondaryDB has not been restored in the last 60, 62, 64 minutes because the data in the SecondaryDB is 3 hours into the future. I’m concerned the restore job is also having the same issue trying to restore 3 hours into the future.
Hi Brian,
I’m deleting your other duplicate question on another post — mostly because you addressed it to Kendra, who doesn’t work here anymore. Oopsie daisy!
Anyway, your best bet for random Q&A is over on dba.stackexchange.com.
Thanks!
Erik,
That is fine. I’m ok with the duplicate. Still have the issue. Still not able to log ship across time zones.
Thanks
bd
Can we take full,diff and log backup of primary database this process break LSA of Log shipping ?
Take backup with copy_only option it will not break the LSN.