Log Sequence Numbers and Restore Planning
.gif "Icon showing a blue database disk")
This topic is relevant for SQL Server databases that use the full recovery model.
For restore planning, the most important log sequence numbers (LSNs) are first and last LSNs. These LSNs can be obtained from the following locations:
- The backupset table in msdb. The columns are named first_lsn and last_lsn.
- The RESTORE HEADERONLY statement. The columns are named FirstLSN and LastLSN.
The following table defines these terms for different backups.
| Term | Definition |
|---|---|
first_lsn or FirstLSN |
Log sequence number of the first or oldest log record in the backup set. For data and differential backups, the first LSN identifies the earliest log record that is needed to perform recovery with this backup. For log backups, the first LSN identifies the first log record included in the backup. |
last_lsn or LastLSN |
Log sequence number of the next log record after the backup set. The last LSN identifies the next log record beyond the end of the backup. For data and differential backups (and for log backups containing bulk-logged operations), roll forward must go at least up to this LSN. Otherwise, the data copied during restore is inconsistent. For log backups, the log backup includes log records up to but not including this LSN. |
Log Sequence Numbers and Data or Differential Backups
For data and differential backups, the log data between first_lsn and last_lsn is included in the backup. This allows the backup to be used without log backups to recover to last_lsn.
For a data or differential backup, last_lsn is the earliest recovery point possible if you use the backup in a restore sequence. If an earlier recovery point is required, an earlier backup must be used.
When you are planning which log backup to use to roll forward after the restore of a data or differential backup, you generally start with the first log backup after that data or differential backup. When you inspect the properties of the backup, you will find a log backup whose first_lsn is less than or equal to the last_lsn from the data or differential backup and whose last_lsn is greater than the last_lsn from the data or differential log backup.
Log Sequence Numbers and Log Backups in a Log Chain
A new log chain starts either with the first full database backup following database creation or after switching from the simple recovery model to the full or bulk-logged recovery model. In the first log backup in a chain, backupset.begins_log_chain = 1.
The first_lsn and last_lsn are used to link log backups into a consecutive sequence (log chain). You can use a sequence of consecutive log backups to roll forward a database from the most recent data or differential backup or from an earlier backup past missing or damaged data and differential backups.
In a log backup, first_lsn is the LSN of the first log record in the backup, and starting with this log record, the log backup includes log records up to but excluding the log record whose LSN is last_lsn. Two log backups are consecutive if and only if the LSN of the last log record in the earlier backup (Backup_A) is greater than or equal to the LSN of the first log record in the later backup (Backup_B); that is, Backup_A.last_lsn >= Backup_B.first_lsn. If this is not true, a gap exists between the two backups.
The significance of the relationship between these LSN is as follows:
- A.last_lsn = B.first_lsn
If A.last_lsn = B.first_lsn, B is usually the log backup taken immediately after A.
This relationship is shown in the following illustration. Notice that log record n, which occurs in log backup B, was recorded as last_lsn in log backup A and as first_lsn in log backup B.
.gif "last_lsn of log backup A=first_lsn of log backup B")
- A.last_lsn > B.first_lsn
If A.last_lsn > B.first_lsn, overlap exists. Overlap typically results from creating a copy-only log backup or the first log backup taken after a point-in-time recovery. The overlap can involve different recovery forks. For more information, see Recovery Paths.
Causes of Broken Log Chains
Generally, the SQL Server Database Engine prevents gaps in the sequence of log backups, keeping the log chain intact. However, a database administrator can break the log chain by changing the recovery model to simple and back to full or bulk-logged.
You cannot roll forward across recovery model changes that involve the simple recovery model, because the log chain is broken. After changing to the full or bulk-logged recovery model, you should take a new differential base or set of differential bases. Alternatively, you can use differential backups to bridge a gap.
See Also
Concepts
Planning and Performing Restore Sequences (Full Recovery Model)
Introduction to Log Sequence Numbers
Viewing Information About Backups
Recovering to a Log Sequence Number (LSN)
Recovery Paths
Transaction Log Logical Architecture
Other Resources
backupset (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)