9

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?

Improve this question
2
  • 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
    Commented Jan 31, 2021 at 4:38
  • The times imply that there's not a missing backup file. Perhaps there's a bug in Microsoft software!!! So that the first transaction log backup takes it's base as the most recent diff rather than the full. Once it's working at the 06 file the first_lsn is correctly the previous log backup not a full or diff. Though, there's a argument to be said for the diff being the correct first_lsn as that's the one that you're supposed to use if everything is perfect.
    – user3710044
    Commented Jul 14, 2023 at 7:52

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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
    Commented Jan 31, 2021 at 0:37
  • @dsuy, add the T-SQL scripts your question that reproduce the problem.
    – Dan Guzman
    Commented Jan 31, 2021 at 3:59
  • Could it be a problem with the log backup? One is corrupted or missing?
    – Triynko
    Commented Aug 4, 2022 at 14:43

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.