Understanding SQL Server Log Sequence Numbers for Backups

By:   |   Comments (9)   |   Related: > Restore


Problem

This tip describes SQL Server Log Sequence Numbers (LSNs) and how the sequence numbers link full, differential and transaction log backups.  We will look at sample backups and how these LSN values can be read from the backup files to determine the restore path.

Solution

This tip is the continuation from this tip, Different Ways to Restore a SQL Server Database, and utilizes the same database creation and backup scripts to explain how the SQL Server full, differential and transaction log backup chain is mapped between each backup type. If you want to follow along, please read this first tip and setup your database and backups.

RESTORE HEADERONLY

When restoring a database, the initial database RESTORE sequence must begin from a FULL database backup. A database RESTORE sequence cannot begin with a differential file backup or transaction log backup. When restoring databases there are four important LSNs: FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN.  These values can be retrieved from a SQL Server backup file using the RESTORE HEADERONLY command.

You can use RESTORE HEADERONLY to retrieve the backup header information for each backup file on disk as shown below. 

USE [master]
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'


SQL Server Log Sequence Numbers (LSNs)

I have collated the FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN for these database backup files (Database setup script) into an Excel spreadsheet as shown below. The full scripts to retrieve the LSNs from all backup files are found at the end of this tip.

"Column A" is the backup type performed at a point-in-time (i.e. F1 = first full backup, T3 = third transaction log backup, D2 = second differential backup).

The full scripts to retrieve LSNs from all backup files are found at the end of this tip. “Column A” is backup type performed at a point-in-time.

Some attributes for the full database backup LSNs are:

  •  The very first full database backup will always have a DatabaseBackupLSN of zero
  •  The very first full database backup's FirstLSN will be the same as the CheckpointLSN

Some attributes of a differential database backup LSNs are:

  • The DatabaseBackupLSN value for the differential backup identifies the full database backup that is required in order to apply the differential database backup
  • The DatabaseBackupLSN value for the differential backup will match its base full database backup CheckpointLSN
  • The CheckpointLSN maps to the CheckpointLSN of the first transaction log backup after the differential backup

Some attributes of a transaction log backup LSNs are:

  • A LSN uniquely identifies every record in a transaction log backup
  • The FirstLSN and CheckpointLSN of the first transaction log backup is also the first full database backup CheckpointLSN if the backup is taken when the database is idle and no replication is configured
  • The transaction log LSN chain is not affected by a full or differential database backup
  • LSN are sequential in nature. A higher LSN value indicates a later point in time

Understanding LSN Mapping Examples in SQL Server

Below are 3 sections that describe the LSN mapping for:

  • Full database backup LSN to Transaction Log backup LSN
  • Full database backup LSN to Differential database backup LSN
  • Differential database backup LSN to Transaction Log backup LSN

Full database backup LSN to Transaction Log backup LSN

Full database backup LSN – Transaction Log backup LSN
  • FirstLSN identifies the first log record included in the backup
  • LastLSN includes log records up to, but not including this LSN
  • When planning which transaction log backup to use to roll forward, the LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup
  • In the example above, Full database backup LastLSN 34000000025600001 falls in between transaction log backup T1 FirstLSN 34000000016000100 and LastLSN 34000000028800000. Applying T1 after F1 will succeed, applying T2 or T3 after F1 will result in an error.
  • A transaction log backup's LastLSN is the FirstLSN in the subsequent transaction log backup chain. In the example above, transaction log backup T1 LastLSN 34000000028800000 is the FirstLSN of transaction log backup T2, transaction log backup T2 LastLSN 34000000030400000 is the FirstLSN of transaction log backup T3 and so on

Full database backup LSN to Differential database backup LSN

Full database backup LSN - Differential database backup LSN
  • A differential database backup can only be applied ONCE to a restored full database backup that has a CheckpointLSN value that is equal to the differential backup DatabaseBackupLSN

Differential database backup LSN to Transaction Log backup LSN

Differential database backup LSN – Transaction Log backup LSN
  • A differential backup LastLSN + 1 will be in between the FirstLSN and LastLSN of its subsequent transaction log backup
  • In the example above, Full database backup LastLSN 34000000035200001 falls in between transaction log backup T3 FirstLSN 34000000030400000 and LastLSN 34000000036000000. Applying T3 after D1 will succeed, applying T4 or another transaction log backup will result in an error.

Script to Retrieve Backup LSNs

This is the full script to retrieve the LSNs for all database backup files created from this tip.

USE [master]
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T3.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T4.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D2.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T5.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F2.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T6.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T7.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D3.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T8.TRN'
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Sunday, April 5, 2020 - 3:43:05 PM - Elvin Ahmadov Back To Top (85286)

Thank you very much for your efford, Simon. I whink there's one mistype in Differential database backup LSN to Transaction Log backup LSN part. "In the example above, Full database backup LastLSN 34000000035200001" must be Differential.

Tuesday, May 1, 2018 - 10:25:06 PM - Simon Liew Back To Top (75836)

Hi Jexinko,
Thank you for your comment.

CheckpointLSN is the log sequence number of the last checkpoint. There's likely an automatic database checkpoint ran prior to T7 log backup.

CheckpointLSN column here is mainly to illustrate the very first full database backup which will always have a DatabaseBackupLSN of zero. Then the FirstLSN and CheckpointLSN of the first transaction log backup is also the first full database backup CheckpointLSN if the backup is taken when the database is idle and no replication is configured.

A full or differential backup forces a checkpoint hence you typically see the log backup CheckpointLSN is the same as the Full\Differential backup CheckpointLSN (when the database is idle and no replication is configured), but over time this can vary.

To check the log restore sequence, always refer to the log LastLSN which should be the FirstLSN of the subsequent log backup. Apology for the long-winded response. Hope this makes sense as to why log backup CheckpointLSN sometimes can be on its own.

Tuesday, May 1, 2018 - 2:13:00 PM - jexinko Back To Top (75834)

 Hi Simon,

 

First i want to thank you for your work. 

Like Paragk said, on line 12 of your excel Datasheat, the CheckpointLSN of transaction log T7 doesn't match anything. Shouldn't it match the F2 CheckpointLSN?

 

Thx

 

Friday, March 17, 2017 - 6:14:44 PM - Simon Liew Back To Top (51349)

Hi Paragk,

Match the full backup CheckpointLSN with log backup DatabaseBackupLSN. In saying this, whats more important is the log backup FirstLSN and LastLSN. A log backup LastLSN and the subsequent log backup FirstLSN will need to match in order to restore them sequentially.  This is described in my initial tip on this topic https://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/

Friday, March 17, 2017 - 4:08:09 AM - Paragk Back To Top (51289)

 Hello Simon,

 IN the LSN table at transaction T7(line no 12).
 CheckpointLSN number is not matcjing with Last Full Backup CheckpointLSN number.

Is is a typo ?

Please check line 10,11,12.

Wednesday, December 9, 2015 - 6:19:11 PM - Simon Liew Back To Top (40226)

Hi Cody,
The DifferentialBaseLSN coincide with DatabaseBackupLSN for differential backup. For non-differential backup types, DifferentialBaseLSN will be null anyway. In multi-based differentials (not a common\good practice, but happens), DifferentialBaseLSN and DifferentialBaseGUID within the differential backup is null so it might not be available to match using GUID.

Referring to https://msdn.microsoft.com/en-AU/library/ms178536(v=sql.110).aspx, DatabaseBackupLSN is the Log sequence number of the most recent full database backup. DatabaseBackupLSN  is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.

So, it is correct to refer DatabaseBackupLSN to CheckpointLSN of a full backup. But I can see your point is also valid to use DifferentialBaseLSN and DifferentialBaseGUID to match the full backup. My personal preference is to look at the mentioned columns in this tip because it applies across different backup types.

I haven't come across circumstances where the LSN matches but the GUID doesn't so I'm not able to comment. But if I were to do a R&D, I'd take differential backups via different devices such as tape vs disk (which is not a common\good practice). But then, this is just a wild guess and a bubble thought. Might be a time waster.

Hope this helps.

Sunday, December 6, 2015 - 10:03:19 PM - Cody Back To Top (40202)

I thought you were meant to map a differential to its base using the differential_base_guid. Are there circumstances where the LSN matches but the GUID doesn't, and generate an error?

Thursday, April 24, 2014 - 6:09:20 AM - Naveen Back To Top (30495)

Thanks Simon Liew for this excellent article!!

Tuesday, April 22, 2014 - 11:20:37 PM - Vashistar Back To Top (30479)

Thanks.. Nice docemnt.














get free sql tips
agree to terms