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'
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).
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
- 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
- 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
- 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
- Take a look at this tip that shows different ways to restore a SQL Server database – https://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/
- Learn more about Log Sequence Numbers and Restore Planning – http://technet.microsoft.com/en-us/library/ms190729(v=sql.105).aspx
