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

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