Sql-server – Do Log Backups depend on Differential Backups

backupsql servertransaction-log

I have the following backup strategy set up:

Backup strategy

My understanding so far, was:
Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.

Based on this understanding, I thought that I would have two alternatives to perform a complete restore to the most recent state.

Alternative #1:
Restore Sequence Number One

This alternative #1 works as expected with no issues.

Alternative #2:
Restore Sequence Number Two

This alternative #2 shows the following error.

Msg 4305, Level 16, State 1, Line 53 The log in this backup set begins
at LSN 144000000122400001, which is too recent to apply to the
database. An earlier log backup that includes LSN 73000000188800001
can be restored.

After analyzing the backup history, I can conclude that if there are Differential backups prior to Log Backups, the latest Differential backup MUST be restored before restoring backups.

View of Backups history

So far, I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.

However, as shown here, perform a complete restoration based only in log backups is not being allowed if DIFF backup hast not been restored, so, my question is:

What is the reason behind DIFF backup being required to allow LOG restores in Restore Sequence #2?

Best Answer

Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.

This is correct understanding. In full recovery, ONLY a transaction log backup will allow log truncation.

I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.

Yes, this is correct as well. Infact, when a log shipping is broken (e.g. some process takes a NON COPY_ONLY log backup), the fastest way to rebuild log shipping is to take a differential backup and restore it to secondary and then take a log backup to establish the log chain.

there's indeed a strong dependency between transaction logs and differential backups. In fact, I wouldn't be able to restore everything If I had lost the latest Differential backup

NO. This is not 100% correct. Diff and t-log backups are independent.

Diff will allow you to skip the log backups that you will need to restore. But to do a Point-in-time restore, you will need a log backup (with STOP AT command to do a point-in-time restore).

For more details, refer to my answer - Log backup vs differential backup if latter is broken

sp_RestoreGene will automatically construct the right sequence for you.

What is the reason behind DIFF backup being required to allow log restores in the Restore Sequence #2?

This is the minimal repo that proves that you do not need a diff backup to allow log restores in the restore sequence.

Related Question