As your databases get bigger, you’re going to run out of time to do nightly full backups. You’re going to be tempted by the siren song of differential backups. The idea is that you’ll do full backups on the weekends, and then differential backups each night of the week.
Differential backups aren’t just the sum total of the transaction logs that have happened since the last full – they’re actually the pages that are different. This can end up being much smaller than the transaction logs if you’re continuously modifying the same pages in the database over and over. (Think a queue table with a few records that are constantly inserted, updated, and then deleted.)
When it’s time to do a restore, you just need:
- The most recent full backup
- The most recent differential backup (not all of them)
- The transaction logs since the differential
Sounds awesome, right? Not so fast.
Danger #1: Missing Full Backups – are you sure you’ve still got that last full backup online? Are you sure the backup’s good? The differential backup is useless by itself, and SQL Server doesn’t check that the full backup is actually online for you. It’s completely up to you. If you’ve set up maintenance plans that automatically delete backups older than X days, you might be deleting your full backups and just keeping the recent differentials around.
Danger #2: More Missing Full Backups – if someone else (because you’d never do this) takes a full backup on production to refresh the development server, you’d better know exactly where that file is. The next differential relies on the most recent full backup – even if you weren’t the one who did it. If you need to do a one-time full backup without affecting your differentials, check out copy-only backups.
Danger #3: Out-of-Control Change Rates – are you the kind of DBA who rebuilds all indexes nightly? Your differential backups can easily be nearly as large as your full backup. That means you’re taking up nearly twice the space just to store the backups, and even worse, you’re talking about twice the time to restore the database. Using differential backups means being much more judicious about index maintenance work. Even if you’re using Ola Hallengren’s excellent database maintenance scripts, you still can’t rest easy – ideally you schedule the index maintenance to happen right before the full backup.
Danger #4: Offsite Backup Complexity – if you need to get your backups offsite as quickly as possible, and you’re copying last night’s backups to tape to move them offsite, you’re not really covered if you’re not including the full backup. Take this scenario:
- Sunday – full backups run and get copied to tape #1
- Monday – differential backups run and get copied to tape #2
- Tuesday – differential backups run and get copied to tape #3
- Wednesday – disaster strikes
To restore, you need both tape #1 and tape #3 brought back from the offsite tape storage. Inserting, mounting, reading, switching, these operations all take time and make your recovery more complex.
Done right, differential backups can save your maintenance window. But if you’re not ready for the additional management discipline they require, they’re worse than no backups at all. They’re a false security blanket that results in heartbreak.
46 Comments. Leave new
The danger I most frequently run into is #3, and not just because of index rebuilds. “We only change 5% of our data every day” is fine, except for the fact that that 5% changes is also resetting date stamps and status bits in dozens of other tables, dirtying those pages as well.
I think the worst example I’ve seen personally was a 500GB database that ended up generating a 800GB differential backup file after only two days….it was pretty easy to convince that group to go back to nightly fulls!
I agree, I’ve always been nervous about using weekly FULLs and daily DIFFs.
One place I do like using DIFF backups are to simplify the LOG restore process.
For example, daily FULLs, then LOGs running every X minutes around the clock. In this case, having a DIFF run 12 hours opposite of the FULL can help you out.
Basically, you’re just cutting down on the number of LOG restores you need to do. But I make sure not to delete any of the LOG files. I look at DIFFs as purely an extra, nice to have, but never something to depend on.
One of my favorites is a combination of #1 and #4.
Full DB backup runs Sunday and is moved to tape via an incremental drive backup.
Differentials run Monday through Saturday.
Space issues or Maintenance plan delete the full backup say Friday.
Saturday is the full tape backup
Oh, and while the tapes for the full backups (tape not db) are kept for several months, the incremental tapes are rotated every few days. Because the full DB backup doesn’t exist on Saturday it isn’t moved to the tape with the full backups. Once your Sunday incremental tape is written over, say Wednesday, you no longer have a full backup.
Almost happened to me once, but we figured out what was going on before an actual disaster.
Or worse, the person in charge of tape backups has failed to let you know that they can no longer copy all backups to tape within a 24 hour range so certain servers haven’t been backed up to tape in 6 months or more. (true story, by the way)
If your full backups are that large and taking that long, couldn’t filegroup backups be an alternate to using nightly diffs? I know, I’m opening a can of worms but I enjoy the influx of comments with great ideas that should be coming in right about… NOW 🙂
I often help in DR discussions around recovery point and recovery time objectives. I wonder how many organizations even with a full backup plus incremental, actually have tested and know how long it would take to recover their database to an operational state that meets their RTO. Not only do you need the data to restore, you need the time to restore it (although restoring “late” is obviously more acceptable than not being able to restore at all).
Just out of curiosity how often does the fact that the backups may or may not be on tape play into RTO calculations? Ahead of time that is. ie Full backup is taken on Sunday and backed up to tape (which is moved off site), then Thursday a maintence plan deletes it as being “old”. The RTO for Friday and Saturday is considerably longer due to the wait on the tape comming in from off site.
Even though you use Ola’s db scripts he updates them as well. So there’s a bit of administration even when you use his scripts plus it takes a while to get all the servers inventories with their schedules of start and completion times.
Even if you use a tool like Litespeed or RedGate’s Hyperback, there’s always something you have to think through.
Gee, thanks Brent…now I have to review all my backup plans and redesign all my schedules…sigh…again…ahh the life of a DBA
Another risk is full backup corruption. You’re taking weekly full backups and nightly diffs. Consider a disaster that occurs on Day 6, one day before the next full backup. You find that the last full backup is corrupted or otherwise unrestorable, making all differentials after that point useless. You then need to go back a further week to the previous full backup (taken 13 days ago), and restore that, plus the differential from 8 days ago, and the subsequent 8 days of transaction logs (assuming none of those ended up corrupted!).
If you’re taking daily full backups, a corrupted full backup only introduce an additional 24 hours of logs to restore.
Alternatively, a log shipped copy of the database could save your bacon (you have a warm standby, and you know the log backups are definitely good).
can some one please let me know what is the affect of transactional , full backups on a index tuned database, this helps to decide on scheduling nightly index maintenance after or before daily backups or in between the last interval of transactional backup schedule (this is my case , inorder to save some time for backups to finish and allow the rest of nightly sql jobs to run before start of business in the morning).
Kiran – I’m not exactly sure what you’re asking. Can you try simplifying your question a little?
HI Brent,
Thanks for the response, I just want to know when is a good time to schedule nightly Index Maintenance jobs including updating statistics, should it be before or after nightly backup plans.
Cheers,
Kiran
Ah, okay. I like doing it before my nightly fully backups. Doing index maintenance can push a lot of data through the transaction logs, and that’s a lot of work I’d have to replay if I want to restore. Therefore, I want it to happen after the business day finishes, with at least one or two transaction log backups happening after this (and before the next full backup.)
That way, if disaster strikes at 11am, I have to restore the full backup and all the t-logs afterwards, but I won’t have to restore the heavy transaction logs from the prior night’s index rebuilds/defrags. Hope that helps!
Perfect Answer Brent , that’s what I’m doing currently …Index maintenance run at 10 PM finishes before 10:30 that’s when a next transactional backup kicks off till 12:30 AM (recurring every two hrs from 4:30 AM – 12:30 AM–I need to do this as we are operating in multiple time zones here in Australia ) , and Full backups and cleanups start at 12:45 AM…glad to know that I’m on right track .
Cheers,
Kiran
Brent,
Is there any phenomenon through which differential backup file would get deleted automatically without any manual intervention? If yes, how can we find out that the differential backup has been deleted.
Sure, if you have jobs set up to delete them.
Thanks Brent.
So, without jobs set up and manual intervention, there is no other way that differential backup gets deleted. So deletion operation is not logged anywhere. Please let me know if I am missing anything here.
Correct, Windows doesn’t log file deletes either.
You need to verify three things:
1. Location of your Fulls, Diffs, and TLog backups
2. The file extension of each type of backups
3. If you have a job/maintenance plan that cleans out old backups, what folder is it targeting.
It’s easy to set up backups together, and forget that there is a maintenance task that cleans up old backups and have those diffs being targeted accidentally. Files don’t just disappear. In my experience, maintenance plans sometimes don’t delete the files and the drive fills up.
Thanks Brent, It helps 🙂
If COPY ONLY backups were taken then step 2 wouldn’t be a problem. With that in mind is there a way to restrict a user to only be allowed to perform backups with the COPY ONLY option?
James – nope. That’s the awful thing about differentials – the person who’s gonna accidentally take that other backup isn’t going to know about the COPY_ONLY option. They never do.
I’m a little late to the party.
(1) As far as “Danger #3: Out-of-Control Change Rates ,” specifically caused by index maintenance, would this also not be a problem with LOG backups? Or is just worse with DIFFERENTIAL because it’s page level?
(2) Also, a lot of my clients are using the SIMPLE recovery model, DIFFERENTIAL is the only other supplemental backup type available in this case. Short of implementing a read-only file group (and of course switching the recovery model to FULL), are there any other options to explore?
John – welcome to the party!
1. No, because log backups only back up the changes once. Differentials continue to back up the changed pages every single time.
2. I’d start by asking what the RPO and RTO are, and that helps design the backup strategy. You can learn more about that here: https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/
Brent, Thanks for the prompt reply! Just to play devil’s advocate, in the case of FULL + DIFFERENTIAL (SIMPLE RECOVERY model), could one not target index maintenance or other “major changes” prior to the next FULL backup? Or could you manually perform a FULL backup (not copy only) after these changes to essentially reset the sequence? OR is this really more trouble than it’s worth?
I enjoy your blog!
Thanks!
John – right, it all comes down to a simple question: what’s the problem that you’re trying to solve?
In the case of index maintenance and major changes, that question is especially important.
Brent,
My case may fall under Danger #3. Differential backups size are more than that of the FULL backups size.
In my case, We are doing
Weekly – Full Backups
Step 1: “Check Database Integrity Task” maintenance step and “Include Index” option is enabled in to it.
Step 2: Full Backups taken
Step 3: Cleanup Backup files process
Nightly,
Step 1: “Check Database Integrity Task” maintenance step and “Include Index” option is enabled in to it.
Step 2: Differential Backups taken
Step 3: Cleanup backup files.
Query: Is “Nightly Step1” the culprit for the larger Differential Backups?
Thanks & regards,
Vijay
Hello Brent,
I like reading your posts.
About the subject , what about a daily full backup with log backups every hour.
Is it a good backup plan?
I re-read this post because I am having issues with huge differentials. SQL 2005, (inherited of course) Full back up weekly, Nightly Diffs, and nightly Tran(yes Odd I agree). In addition, Nightly Reorg Index, every other day defrag and shrink files & db and a whole bunch of other stuff that shouldn’t be done. anyways, since we failed over for patching, the diffs have been huge, and I can’t figure out 100% why.
I have ceased the nightly reorg, and the every other day day maintenance, and I wanted to check if there was anything else to look at.
Susan – it’s tough for us to troubleshoot that kind of thing in a blog post comment, unfortunately.
I know, it hard to troubleshoot in person. I was only looking for vague directions like Look east, to see the sun (in the am) lol . not looking for the answer of WHY, just if there were any more bases I need to check out there that I hadn’t mentioned.
Hey Brent,
You link to ‘Check out our online training, Backup & Restore, Step by Step. (https://www.brentozar.com/training-videos-online/sql-server-backup-and-recovery-step-by-step/)’ results in a 404.
Yeah, we no longer offer that course. Thanks!
Thanks Brent!
There’s something to keep in mind if you use Ola’s scripts regarding copy_only backups and such. That is, it’s possible that log backups your oldest full backup is dependent on, can be automagically deleted, if a full copy_only backup is taken.
For example, assume a cleanup time of 1 hour (@CleanupTime = 1):
1:00 – Full_Backup_1
1:01 – Log_Backup_1
1:31 – Log_Backup_2
2:01 – Log_Backup_3
2:02 – Copy_only_Backup_1
2:31 – Log_Backup_4
2:31 – Log_Backup_1 is now deleted, since it’s passed the @CleanupTime.
The SP DatabaseBackup has a check to make sure log backups aren’t deleted if they are dependent on the oldest full or diff backup (even if they are older than the @cleanuptime). But the code that does this doesn’t look at copy_only’s.
In the example above, if the copy_only backup wasn’t taken, then Log_Backup_1 would not be deleted even though Log_Backup_1 is older than the @CleanupTime.
This is the code that checks it:
SELECT @CurrentLatestBackup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE ([type] IN(‘D’,’I’)
OR database_backup_lsn = the earliest full or diff backup, then change the above block of code to this:
SELECT @CurrentLatestBackup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE ([type] IN(‘D’,’I’)
OR database_backup_lsn < @CurrentDifferentialBaseLSN)
AND is_damaged = 0
AND database_name = @CurrentDatabaseName
and is_copy_only = 0
I like to keep all log backups since the earliest full backup, regardless if a differential backup exists. This way any point in time recovery is possible since the earliest full backup. For this change, the block would be:
SELECT @CurrentLatestBackup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE [type] = 'D'
AND is_damaged = 0
AND database_name = @CurrentDatabaseName
and is_copy_only = 0
I know logs can be restored to copy_only full backups, but typically when a copy_only backup is taken (in my environment at least), it’s a “one off” backup, not intended to be part of the core backup chain. Usually the copy_only is taken and it’s gone forever, never to be seen again. Anyway, those are my thoughts on this.
I am guessing this is all under the premise of db’s that are > 1 TB, right? Otherwise, nightly backups with 4 hour differentials and 15 min (or less depending on RPO) transaction log back ups should suffice for a highly transactional databases. Or is that passe? I work with systems that support emergency services. And I can tell you, I want those differentials when a db needs to be restored that affects patient or officer safety.
Jason – you did SUCH a good thing when you said “or less depending on RPO” – that right there tells me you’re already way ahead of the game. Good thinking!
Brent, good article.
Quick question for you. I recently ran into this problem due to storage issue. One of my databases got corrupted on 11/9. So my full clean backup was taken on 11/8. I did not notice the corruption until 11/15. And no backups were taken after the full clean backup. Would it have worked to recover and remove the corruption if I took differential backup at 11/15 – restore FULL from 11/8 and restore diff from 11/15? Would the corrupted table be removed after the restore? Thanks
Glen – for Q&A, head on over to a Q&A site like https://dba.stackexchange.com.
In my humble small office, long ago, the tape backup system that had seems reliable proved worthless when actual need for a restore occurred; another larger owner had an even more disastrous network loss when the offsite backup provider’s tape system proved faulty as well. I learned to trust only redundant drive images, and was able to simply swap a cloned drive and immediately get back to a running machine with only the loss of hour’s entries. How often do businesses herein actually run full recovery simulations from the tapes being trusted? I found tape backups to be not only slow, but per GB ridiculously expensive, fragile, not at all archival or dependable. Verifying HDD is apparent, while the act of verifying a tape may actually destroy it. Just a notion to consider. Also, full restoration from any imaging backup restores the unit exactly the way it appeared when the image was taken, errors, deletions, viruses and all. Most modern programs allow for restoring folders and single files from any image as well, so you can recover files if an earlier backup is available. A “differential” backup is a FULL backup – just one that the software can make smaller, by utilizing the prior full backup as a linked basis for comparison. Generally by size, 1 Full < 1 Full + 1 Diff < 2 Full backups. Without its linked Full backup, the Diff is useless; a prior Full backup has no link to the Diff so is of no help.
A point of clarification: Differential backups contain the *extents* that have changed since the last Full backup, not just the pages. So if one bit on one page is changed, the entire 64kb extent containing that page gets backed up by the Differential. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server
Two fun backup experiences…
1. Pre-Upgrade Backup… Then the system was Upgraded. And then, during testing, we noticed a problem and wanted to revert but it turned out that manually running the Scheduled Daily Backup job without disabling the schedule sucks when the scheduled backup runs half way through the upgrade process…
2. (I suspect this was because of the first incident since this happened about a year later on the same system) It is important to check that the Scheduled Daily Backup job is turned back on after an upgrade is completed… It took three days before we noticed that the Reporting Database was out of date. Speaking of which, Restoring the Daily Backup to a reporting Database is a GREAT way to verify that the backups are done and are usable – If we didn’t have that reporting database we might still not have noticed ! We now have a script that checks that the ‘standard’ schedule has not changed so that we can catch this sort of mistake more reliably in future.
Why does differential backup can be taken without taking a base full backup? I recently restored a full backup to a different instance and was able to take differential backup from the database without taking a base full backup. However, log backup failed as it required a base full backup. This is a strange behavior for me as I was under the assumption that both differential and log backups requires full backup first.
Daniel – for general questions, head to a Q&A site like https://dba.stackexchange.com.
Add a new fun scenario into this picture… MSSQL on AWS RDS. Full and Differential native sql backups are allowed sure, but those handy dandy RDS Snapshots that can fire off automatically at any time in the background (depending on certain activities occurring on your RDS instance as well as configuration choices) will… rebase(?) your full backup set. So go ahead and take a diff, but for all you know, unless you are checking backupset ahead of time, it could be based to the last native full you took, or it could be tied to an RDS snapshot. May be odds be ever in your favor 🙁
Hahaha, yeah – you wouldn’t want to build a solution on top of export backups in RDS.