Log Sequence Number

[Total: 14    Average: 4.4/5]

Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occurred after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN and LastLSN values. To find out all backup’s LSN values you need to look at its header using the following syntax:

RESTORE HEADERONLY FROM DISK = 'full.bak' 

To understand what these LSNs mean in each database backup let’s consider a backup sequence created (the database uses full or bulk-logged recovery model) using the following commands:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP LOG your_database TO DISK = 'log.bak'
BACKUP LOG your_database TO DISK = 'log1.bak'
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
BACKUP LOG your_database TO DISK = 'log2.bak'
BACKUP LOG your_database TO DISK = 'log3.bak'
BACKUP DATABASE your_database TO DISK = 'diff1.bak' WITH DIFFERENTIAL
BACKUP DATABASE your_database TO DISK = 'full1.bak'
BACKUP LOG your_database TO DISK = 'log4.bak'

This shows that we have created three different types of database backups: Full backup, Differential backup and Transaction Log backup. Now let’s check their LSNs and compare them:

RESTORE HEADERONLY FROM DISK = 'full.bak' 
RESTORE HEADERONLY FROM DISK = 'log.bak' 
RESTORE HEADERONLY FROM DISK = 'log1.bak' 
RESTORE HEADERONLY FROM DISK = 'diff.bak' 
RESTORE HEADERONLY FROM DISK = 'log2.bak' 
RESTORE HEADERONLY FROM DISK = 'log3.bak'
RESTORE HEADERONLY FROM DISK = 'diff1.bak' 
RESTORE HEADERONLY FROM DISK = 'full1.bak'
RESTORE HEADERONLY FROM DISK = 'log4.bak' 

These commands produce the following results:
LogSequenceNumber

where:

  • FirstLSN – Log sequence number of the first transaction in the backup set
  • LastLSN – Log sequence number of the next log record after the backup set
  • CheckpointLSN – Log sequence number of the last checkpoint
  • DatabaseBackupLSN – Log sequence number of the last full database backup

How do FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN Relate to Each Other

The DatabaseBackupLSN of the first full backup is always equal to zero:Full(DatabaseBackupLSN)

The FirstLSN of the first full backup is the same as the CheckpointLSNFull FirstLSN and CheckpointLSN

Full database backup is the main type on which all other backups (especially differential backups) are dependent. In the restore sequence a differential backup is always restored after the full backup. In order to determine the full backup to which differential backup relates, you need to check the values of DatabaseBackupLSN of differential backup and CheckpointLSN of the full backup – their values should be identical.: Full and Differential LSN

Full CheckpointLSN and Differential DatabaseBackupLSN

But what if there is no differential backup, and there is a need to restore the database with the full and transaction log backups? To find out which transaction log backup should be restored right after the full backup is restored take a look at the LastLSN of the full backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the full backup should be between the FirstLSN and the LastLSN of the transaction log backupFull and Tran.Log

Likewise, in order to find out which transaction log backup should be restored after the differential backup you need to pay attention to the LastLSN of the differential backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the differential backup should be between FirstLSN and LastLSN of the transaction log backup followed right after it: Differential LSN and Tran.Log LSNThe CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup:

Differential CheckpointLSN and Transaction Log CheckpointLSN

First transaction log backup has the same FirstLSN and CheckpointLSN as the first full database backup’s CheckpointLSN:Full CheckpointLSN and Transaction Log FirstLSN and CheckpointLSN
For a transaction log backup, the LastLSN value is the same as the FirstLSN of the next transaction log backup:Transaction Log LastLSN and Transaction Log FirstLSN
Transaction Log LSN

Note that LastLSN value of the previous transaction log backup is equal to LSN of the first log record stored in the following transaction log backup, as shown on the picture above.