| |
 |
Log
sequence numbers, or LSN, are assigned to each record in the
transaction log.
When you perform a backup, certain LSN values are stored both in
the file itself, and in the msdb..backupset
table.
You can retrieve the LSN values from a backup file using the
RESTORE
HEADERONLY syntax
e.g.
| | | | NOTE: In SQL Server 2000, there is a column named DifferentialBaseLSN.
In SQL Server 2005, the same column is named DatabaseBackupLSN.
The right column name should be DatabaseBackupLSN, as that is what
it represents, and is also what is stated in the SQL Server 2000
Books Online documentation. | | | |
The FirstLSN
value
is the log sequence number of the first transaction in the backup
set. The LastLSN
value
is the log sequence number of the last transaction in the the
backup set. The CheckpointLSN
value
is the log sequence number of the most recent checkpoint. The
DatabaseBackupLSN
is the
log sequence number of the most recent full database backup.
So, what's the use of the LSN values to us? For a differential
backup set, the DatabaseBackupLSN
value
tells us which full database backup is required in order to apply
the differential backup. You need to look for a full database
backup file that has a CheckpointLSN
value
equal to the differential backup's DatabaseBackupLSN
value.
For a transaction log backup, the FirstLSN
and
LastLSN
values
help us to sort the transaction log files in sequence, during a
restore process.
If a database is in a restoring state, waiting for additional
transaction logs to be restored, how can you tell its current
LastLSN value so that you know which log to apply next? Well, the
msdb..restorehistory
table
stores every restore that has been made to the database. By
referencing the msdb..backupset
table,
you can then find out the last transaction log backup that was
restored, and also its LastLSN value e.g.
SELECT TOP 1 b.type, b.first_lsn, b.last_lsn,
b.checkpoint_lsn, b.database_backup_lsn
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id =
b.backup_set_id
WHERE a.destination_database_name = 'AdventureWorks'
ORDER BY restore_date DESC |
Document history
| 6/27/2008 | Initial release. |
|
