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.

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