Compatible from SQL Server 2008
Find backup to complete your restore operation with missing LSN. Sometimes when you have to restore database to point in time you need to find all backup files from backup chain.
When you are missing some continual log records identified by log sequence number, Management Studio will give you an error with information about missing LSN. You can use this script to find where is the backup containing this LSN stored.
Example of Database Recovery (source: SQL Server Backup Academy)
WARNING: Sometimes you get information about backup location but it is already gone. Then you need to ask for restoring it from long term backup storage if any. It is your duty to have right backup strategy to be prepared to fulfill customer agreement!
DECLARE @FirstLSN BIGINT = 73000002082400198 -- first record in backup by LSN DECLARE @LastLSN BIGINT = 73000002090800001 -- last record in backup by LSN SELECT b.physical_device_name, a.backup_start_date, a.first_lsn, a.last_lsn FROM msdb.dbo.backupset a INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = '' AND a.first_lsn <= @FirstLSN AND a.last_lsn >= @LastLSN
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| physical_device_name | nvarchar(260) | Physical name of the backup device. Can be NULL. This field is shared between backup and restore process. It may contain the original backup destination path or the original restore source path. Depending on whether backup or restore occurred first on a server for a database. Note that consecutive restores from the same backup file will not update the path regardless of it's location at restore time. Because of this, physical_device_name field cannot be used to see the restore path used. |
| backup_start_date | datetime | Date and time the backup operation started. Can be NULL. |
| first_lsn | numeric(25,0) | Log sequence number of the first or oldest log record in the backup set. Can be NULL. |
| last_lsn | numeric(25,0) | Log sequence number of the next log record after the backup set. Can be NULL. |