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.