Understanding the LSNs in Backups

 
Understanding the LSNs in Backups :


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'





ü  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'


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.