Blog

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

Danger is my middle name.

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.

Interested in learning more? Check out our online training, Backup & Restore, Step by Step.

↑ Back to top
  1. 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!

  2. 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.

  3. 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.

  4. 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)

  5. 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 :)

  6. 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.

  7. 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

  8. Pingback: Something for the Weekend - SQL Server Links 07/12/12

  9. 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).

  10. Pingback: Fun with Differentials and Veeam | FradenSQL

  11. 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!

  12. 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

  13. 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.

  14. 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.

  15. Thanks Brent, It helps :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php