Recovering to a Log Sequence Number (LSN)
.gif "Icon showing a blue database disk")
This topic is relevant only for databases that are using the full or bulk-logged recovery models.
SQL Server 2005 lets you use a log sequence number (LSN) to define the recovery point for a restore operation. This is a specialized feature that is intended for tools vendors and is unlikely to be generally useful.
Transact-SQL Syntax for Restoring to an LSN
By using a RESTORE statement, you can stop at or immediately before the LSN, as follows:
- Use the WITH STOPATMARK ='lsn:<lsn_number>' clause, where lsn:<lsnNumber> is a string that specifies that the log record that contains the specified LSN is the recovery point.
STOPATMARK roll forwards to the LSN and includes that log record in the roll forward. - Use the WITH STOPBEFOREMARK ='lsn:<lsn_number>' clause, where lsn:<lsnNumber> is a string that specifies that the log record immediately before the log record that contains the specified LSN number is the recovery point.
STOPBEFOREMARK rolls forward to the LSN and excludes that log record from the roll forward.
Typically, a specific transaction is selected to be included or excluded. Although not required, in practice, the specified log record is a transaction-commit record.
For more information about how to use LSNs, see Log Sequence Numbers and Restore Planning.
For more information about point-in-time restores, see Restoring a Database to a Point Within a Backup.
Examples
The following example assumes that the AdventureWorks database has been changed to use the full recovery model.
RESTORE LOG AdventureWorks FROM DISK = 'c:\adventureworks_log.bak'
WITH STOPATMARK = 'lsn:15000000040000037'
GO
See Also
Concepts
Applying Transaction Log Backups
Introduction to Log Sequence Numbers
Other Resources
RESTORE (Transact-SQL)
Implementing Restore Scenarios for SQL Server Databases
Restoring a Database to a Point Within a Backup
Working with Restore Sequences for SQL Server Databases