SQL Server – FULL Recovery and Differential Backups

backuprecoverysql server

I am going to begin transitioning several of our databases to FULL recovery from SIMPLE. I asked a question last week regarding deployment strategies and I have one additional question.

Consider this:

  • Sunday (midnight) – Run FULL backup
  • Monday-Saturday (midnight) – Run differentials
  • Hourly – Transaction log backup

Are the differential backups basically eliminating the need for the transaction logs for that day?

So, if we have a catastrophic failure at 10pm on Tuesday, we would:

  1. Run tail log backup on affected DB(s)
  2. Restore FULL backup from Sunday night
  3. Restore DIFF backup from Monday night
  4. Restore using log chain from the day on Tuesday up to disaster time.

If this is the case, then could we simply remove the transaction log backups from Monday as part of the maintenance plan, since we have a differential from Monday night? Would it be smarter to keep them for redundancy in case the DIFF backup fails?

Best Answer

There are multiple ways to get to the intended target recovery point with your setup.

A few things:

  1. You cannot eliminate log backups for the most important reason of transaction log re-use.
  2. It's not possible to know when an issue will occur and thus having multiple ways to get to the RPO is useful and sometimes necessary.
  3. Differentials aren't a substitute for log backups, they will lower the RTO as they are much faster to apply than log backups but ultimately aren't worth much on their own (spanning broken lsn points, etc, is useful)
  4. How are you going to know which ones you need and don't need ahead of time?

I would not stop taking log backups but you could put into place an aging mechanism in your backup software or process to eliminate files that are no longer needed.

Using your example, you have many paths but the basis is as such:

Always take a tail of the log backup first

  1. Restore the full backup, latest differential, all logs.
  2. Restore the full backup, all transaction logs.

This is more flexible as a corrupt backup file (say a log file) could be spanned by a differential or a corrupt full could be spanned by differentials, etc.

I would not change how your strategy is currently, but I would make sure it meets your RPO and RTO requirements.

Related Question