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
CREATE TABLE test.[dbo].[test_table]([c1] [nchar](10) NULL)
insert into test.dbo.test_table values (1)
backup database [test] to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_1.bak'
insert into test.dbo.test_table values (2)
BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_1.bak' WITH DIFFERENTIAL
insert into test.dbo.test_table values (3)
BACKUP LOG [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog1.trn'
insert into test.dbo.test_table values (4)
BACKUP LOG [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\tlog2.trn'
BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_2.bak' WITH DIFFERENTIAL
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'
ü 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)
backup database [test] to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\full_backup_2.bak'
backup database [test] to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\differential_backup_3.bak' WITH DIFFERENTIAL
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'
Now if we try restoring differential backup 3 on top of full backup 1, we will get the error:
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
Karthick Krishnamurthy, Technical Lead, Microsoft India GTSC
Pradipta Das, Technical Lead, Microsoft India GTSC
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?
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.
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?
It was helpfull.
Nice explanation :)
Very easy to understand this article....thanks
Very Good and Detailed Explanations:)