Udostępnij za pośrednictwem

How do I map a differential/transaction log backup to its full backup without using MSDB?

  • Artykuł

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process.

“This differential backup cannot be restored because the database has not been restored to the correct earlier state”

 

You know that any differential/T-log backup is mapped to particular full backup. You will encounter above error message when there is mismatch in the LSN (Log Sequence Number) chain between the full backup and differential backup. The best way to understand the chain between differential, transaction log and full backup would be to check the backupset table in msdb database. But what if you don’t have access to msdb anymore and all you have is database backups?

 

Let us understand how to get the backup LSN chains of backup sets using restore headeronly. 

 

FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN can be verified from the available backup sets. They can then be used to establish the backup chain.

These columns can be checked by running RESTORE HEADERONLY FROM DISK = ‘<filename with complete path>’

 

Let’s have a hands-on experience on this.

 

create database test

go

CREATE TABLE test.[dbo].[test_table]([c1] [nchar](10) NULL)

go

 

insert into test.dbo.test_table values (1)

go

backup database [test] to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_1.bak'

go

 

insert into test.dbo.test_table values (2)

go

BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_1.bak' WITH DIFFERENTIAL

go

 

insert into test.dbo.test_table values (3)

go

BACKUP LOG [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog1.trn'

go

 

insert into test.dbo.test_table values (4)

go

BACKUP LOG [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog2.trn'

go

 

BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_2.bak' WITH DIFFERENTIAL

go

 

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_1.bak'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog1.trn'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog2.trn'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_1.bak'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_2.bak'

 

 clip_image001

ü  As seen above, the First LSN of 1st transaction log to be restored matches the CheckpointLSN in the full database backup. From there onwards, you can determine the serial order such that the LastLSN of T-Log backup 1 matches FirstLSN of T-log backup 2 and so on. This is because Transaction Log backups are sequential in nature.

 

ü  For differential backups, you can notice that their DatabaseBackupLSN should be the same as the CheckpointLSN in the full database backup.

Also note that as differential backups are cumulative in nature, restoring the latest differential backup (identified by larger CheckpointLSN) will save some time in the Restore process.

 

Then I take a new full database backup and another differential backup on top of it.

 

insert into test.dbo.test_table values (8)

go

backup database [test] to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_2.bak'

go

backup database [test] to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_3.bak' WITH DIFFERENTIAL

go

 

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_1.bak'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_2.bak'

restore headeronly from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_3.bak'

 

 

clip_image002

 

 

Now if we try restoring differential backup 3 on top of full backup 1, we will get the error:

 

“This differential backup cannot be restored because the database has not been restored to the correct earlier state”

 

This is because the DatabaseBackupLSN of Differential Backup 3 does not match the CheckpointLSN of Full Backup 1 as shown above.

 

To summarize, the DatabaseBackupLSN for a differential/transaction log backup should match the CheckpointLSN of a full backup for a successful restore; also the FirstLSN of a T-Log backup should match the LastLSN of the previous T-Log backup for the restore to succeed.

I hope this helps you figure out the cause behind such errors with some conclusive data; so that you can proceed further with your next actions in the restore process!

Author – Deepesh Jethwani, Support Engineer, Microsoft India GTSC

Reviewers –

Karthick Krishnamurthy, Technical Lead, Microsoft India GTSC

Pradipta Das, Technical Lead, Microsoft India GTSC

Comments

  • Anonymous
    July 16, 2013
    what if we restore the database to some point in time and continue taking trn log backups? Don't we get different recovery paths and a the possibility to choose between them?

  • Anonymous
    July 17, 2013
    Hi Thierry, To summarize if I understood your question correctly, you intend to restore the source database to a point-in-time on a new destination database but continue taking transaction log backups on the Source database. If that is the case, you are correct. There will be two recovery paths:

  1. Using full backup of source database and all its subsequent transaction-log backups. OR
  2. Using full backup of destination database(which needs to be taken) and all its subsequent transaction-log backups corresponding to this new full backup. Let me know if this answers your question.

  • Anonymous
    October 22, 2013
    Great Article. I had an idea this was the case but didnt know how exactly. See Im trying to do differential backups on a database with regular full baackups. Can I map my differentials to a particular base bakkup when backing up?

  • Anonymous
    November 19, 2013
    Deepesh, It was helpfull. Thanks Chaithanya

  • Anonymous
    January 23, 2014
    Nice explanation :)

  • Anonymous
    March 12, 2014
    Very easy to understand this article....thanks

  • Anonymous
    April 13, 2014
    Very Good and Detailed Explanations:)

  • Anonymous
    August 21, 2014
    What about the scenario where you have restored a FULL backup and applied a DIFF backup? How do you determine which LOG backup to continue with? I have seen a few incidents where the FirstLSN of a log's HEADERONLY information matches with either the CheckPointLSN or LastLSN of the DIFF backups, and this confuses me a bit.

    • Anonymous
      May 17, 2017
      start applying log backups which start from lastlsn of differential backup