Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
Perfect, that's exactly what I needed to know. Thanks so much! –  Kris Gruttemeyer Jul 21 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 Jul 21 at 19:10
    
@KrisGruttemeyer You only ever need the latest Differential based on the same full or differential base lsn. –  Sean Gallardy Jul 21 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 Jul 21 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 Jul 21 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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