6

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?

1
  • 1
    I think you have error with #2 is is because you miss a tlog backup before log1. You can actually check the first_lsn, last_lsn, database_backup_lsn from msdb.dbo.backupset and see whether log1's first lsn is >= full1's last lsn. – jyao Jan 31 at 4:38
5

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.

2
  • Thanks. I have edited to question to clarify it a bit more. What is the reason behind DIFF backup being required to allow log restores in the Restore Sequence #2? – dsuy Jan 31 at 0:37
  • @dsuy, add the T-SQL scripts your question that reproduce the problem. – Dan Guzman Jan 31 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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