4

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?

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
5
  • Perfect, that's exactly what I needed to know. Thanks so much!
    – Kris Gruttemeyer
    Commented Jul 21, 2014 at 17:09
  • Additionally, in your example you said if a log gets corrupt. If that were to happen, can you do FULL -> Logs for one day -> Differential for next day -> logs to point in time?
    – Kris Gruttemeyer
    Commented Jul 21, 2014 at 19:10
  • @KrisGruttemeyer You only ever need the latest Differential based on the same full or differential base lsn.
    – Sean Gallardy
    Commented Jul 21, 2014 at 19:24
  • So, differentials are not tied to each other (LSNs), instead each differential file's start LSN is the last LSN from the full? New DBA here so i just want to make sure I understand everything.
    – Kris Gruttemeyer
    Commented Jul 21, 2014 at 19:29
  • 2
    @KrisGruttemeyer You could think of it like that. Let me explain it another way. Differentials are only good from the base that they are taken from (full) because they only include the extents that have changes SINCE the full backup was taken. The only thing that differentials have in common (from the same full backup) is that they will reflect the changes at different times from the full, otherwise they aren't connected in any way. Differentials (full database) can't span full backup borders.
    – Sean Gallardy
    Commented Jul 21, 2014 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.