SQL Server database transaction log sequence number (LSN) Introduction

Original: http://blog.csdn.net/tjvictor/article/details/5251463

?

? Log Sequence Number (LSN) is the number of each record in the transaction log.

When you perform a backup, some LSN values are stored both in the file itself and msdb: The Backupset table. You can use the RESTORE headeronly syntax to get the LSN value from the backup file .

?

Note: in SQL Server , there is a column called DIFFERENTIALBASELSN . in SQL Server 2005 , however, the same column name becomes databasebackuplsn. The column name of the positive solution should be databasebackuplsn, the name to express its meaning, you can from SQL Server Find instructions in Books Online.

FirstFSN lastlsn Span style= "font-family: Arial" The value is the last one. checkpointlsn checkpoint log serial number. databasebackuplsn

so LSN What value does it have for us? For differential backup devices, thedatabasebackuplsn value tells us which differential backups are required for a full backup of the database. You need to find all full and differential backups with the same CHECKPOINTLSN value.

for a transaction log backup, during the database restore process, the FirstLSN and the LastLSN value can help us sort the transaction log files by ordinal.

If the database is in a restored state and is waiting for another transaction log to be restored, how can you tell the current LastLSN value so that you know for yourself which log should be applied next? It's okay,msdb. The RestoreHistory table stores each of the database restore information. You can refer to msdb. Backupset table, you can find the last transaction log backup of the current storage transaction log, and its lastlsn query statement is as follows:

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

This article is translated from Sqlbackuprestore For more highlights, please visit http://www.sqlbackuprestore.com

SQL Server database transaction log sequence number (LSN) Introduction