This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I know this is not a new topic, but many SQL Database Administrator doesn’t know about the importance of LSNs, so I am sharing one of the important discussion about the SQL Server LSNs which are very necessary for Database backup and restore activities.
What is LSN?
Every record in the SQL Server transaction log is uniquely identified by a Log Sequence Number (LSN).
The LSNs are sequential, and in the particular ordered, new LSN is always higher than the old LSN.
The LSN is a type of Numeric(25,0), so we can compare different LSNs using equality operators.
The importance of LSN:
Generally, when we are restoring database, we are restoring in this sequence: Full Database backup -> Differential backup -> Transaction Log backup.
We cannot restore the Differential backup before the Full database backup because LSN is sequentially starting from the full database backup.
Before restoring of the Differential backup and Transaction Log backup, it checks FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN columns.
If LastLSN of the first file and FirstLSN of the second file doesn’t match, it fails to restore Differential backup and Transaction log backup.
The sequence of LSNs is critical to restoring your database because all different database backup files virtually connected with each other.
The first full database backup will always have a DatabaseBackupLSN of zero and FirstLSN is same as CheckpointLSN.
The Full database backup CheckpointLSN is a
DatabaseBackupLSN for all differential databases.The LastLSN of Differential backup will be between FirstLSN and LastLSN of the Transaction Log backup.
