Sql-server – Log backup vs differential backup if latter is broken

backupsql server

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).

Best Answer

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 :

Related Question