Home » SQL » Know About SQL Server Log Sequence Numbers for Backups

Know About SQL Server Log Sequence Numbers for Backups

👤 Naveen Sharma | Modified: May 8th, 2023|SQL | 4 Minutes Reading

Overview

Transaction log is exclusively identified by log sequence number (LSN) in SQL Server. Whenever the users take, the backup of transaction log then, it is stored in LSN form. These are used as internally during a RESTORE sequence to track the path of data, which has been restored. When the backup is restored then, the data is restored to corresponding LSN to the point at which the backup was taken. The LSN of log record on which a given backup and restore events are occurred is view-able by using the discussed command in the following section.

Restore Header Only

While restoring the database, the RESTORE sequence of the initial database begins from FULL database backup. It does not begin with transactional log and differential file backup. There are four important Log Sequence Numbers while restoring the database as mentioned:

  • FirstLSN
  • LastLSN
  • CheckpointLSN
  • DatabaseBackupLSN

User can retrieve these values from backup file of SQL Server by using RESTORE HEADERONLY command. Some attributes of various database backups are discussed below.

Full database backup LSNs

  • It will always have Database Backup LSN of zero.
  • The FirstLSN will be same as the Checkpoint LSN

Differential database backup LSNs

  • It identifies the complete database backup, which is required to apply the backup of differential database.
  • It will match its base full database backup Checkpoint LSN

Transactional log backups LSNs

  • Each record in transaction log backup is exclusively identifies in LSN
  • If the backup is taken when database is idle and there is no replication, which is configured. Then, FirstLSN and Checkpoint LSN both are the first full database backup of Checkpoint LSN.
  • Full or differential database backup does not affect the chain of transaction log LSN
  • Higher value of LSN indicates a later point in time.

Log Sequence Numbers and Log Backups

A new log chain starts with full database backup by following the database creation or after switching from simple recovery model to bulk or full-logged recovery model. The first log backup in a chain is backupset.begins_log_chain = 1.

The first_lsn and last_lsn are used to link the backups of log into consecutive sequence of log chain. User can use the sequence of consecutive log backups to roll forward a database from the differential backup, recent data, damaged data or earlier backup of past missing data. The first_lsn is the LSN of the first log record in the log backup and beginning with this, it includes log records up, but excluding the records of log, whose LSN is last_lsn. There are two consecutive backups log if last record of earlier backup, i.e. Backup_C is greater than or equal to the first log record, i.e. Backup_D. This will be more understandable as Backup_C.last_lsn >= Backup_D.first_lsn. If this condition is false then, there will be a gap between two backups. The importance of relationship between these Log Sequence Numbers is as followed:

    • C.last_lsn = D.first_lsn

If C.last_lsn = D.first_lsn then, D is the log backup that is taken directly after C.

    • C.last_lsn > D.first_lsn

If C.lst_lsn > D.first_lsn, then, overlap exists.

Overlap results in creating a copy-only log backup or the first log backup that is taken after a point in recovery time. It can involve various recovery forks.

Conclusion

Log sequence number for backup is important to understand while working with SQL Server. As it helps to restore the data from a disaster situation by using various commands, out of which one command is discussed above. This command helps to restore the headers part from various database backups. Along, with this, concept of log sequence numbers and backups are discussed.