Disaster recovery - Get backup by LSN

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.

Backup sequence

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:

Results screen

Script results detailed description:

Column nameData typeDescription
physical_device_namenvarchar(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_datedatetimeDate and time the backup operation started. Can be NULL.
first_lsnnumeric(25,0)Log sequence number of the first or oldest log record in the backup set. Can be NULL.
last_lsnnumeric(25,0)Log sequence number of the next log record after the backup set. Can be NULL.

Leave a Reply

Your email address will not be published. Required fields are marked *