5

Under full recovery mode, will a differential backup "break" the previous log backups?

Let me give an example: Let's say we have the following backups:

  • FULL backup 1
  • Log backup 1
  • LogBackup 2
  • DIFF backup 1
  • LogBackup 3
  • LogBackup 4

Normally to restore in this situation, one would restore as follows:

  • FULL backup 1
  • DIFF Backup 1
  • LogBackup 3
  • LogBackup 4

My question is as follows: If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups? Like this:

  • FULL backup 1
  • LogBackup 1
  • LogBackup 2
  • LogBackup 3
  • LogBackup 4

Any help is appreciated. If this has been answered in another post, please let me know (I tried searching).

Improve this question
2
  • Yes, you can restore from just the log backups if the differential is unavailable.
    – Queue Mann
    Commented Jun 10, 2016 at 13:16
  • @QueueMann Thank you. If you write your comment as an answer, I can mark it as such. (Upping your reputation.)
    – GHauan
    Commented Jun 10, 2016 at 13:32

1 Answer 1

Reset to default
7

Under full recovery mode, will a differential backup "break" the previous log backups?

No. A differential backup will not break the log chain. Log backups rely on LSNs (Log Sequence Numbers).

A log chain is broken when :

  • You take adhoc log backup without using COPY_ONLY option.
  • When you change the recovery mode e.g. from full to simple.
  • When you use TRUNCATE_ONLY or NO_LOG (Both deprecated in SQL 2008) or backup to NUL (its like taking a backup and deleting it !)
  • When you revert the database from a database snapshot. For this scenario, you have to take a full backup - even differential wont work !

If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups?

Yes, provided the log backups are not corrupted as well. Since differential got corrupted, there are chances that your subsequent backups might be corrupted.

You can even re-initialize a secondary log shipping database from a differential backup and then start applying log backups if your log backup chain is broken & provided a full backup has not ran (or you ran a full backup with COPY_ONLY option).

You have to be careful, that running a FULL backup without COPY_ONLY will reset the differential base as each differential backup will contain changes since the LAST full backup.

Read these answers as they are relevant :

Improve this answer

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.