This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I shared the importance of LSN for all the database backup files of the SQL Server.
When we have different transaction backup data, sometimes it is required to check LastLSN and FirstLSN of those files.
If LSN sequence is not proper, then we cannot restore all database files in the series, and it generates an error like, LSN doesn’t match with the previous file.
Below are few different scripts to find LSN information of the Database Backup Files.
Using backupset table:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT database_name ,backup_size ,first_lsn ,last_lsn ,checkpoint_lsn ,database_backup_lsn ,database_creation_date ,backup_start_date ,backup_finish_date FROM msdb.dbo.backupset |
Using sys.database_recovery_status:
|
1 2 3 4 |
SELECT last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = DB_ID() |
Using fn_dblog():
It returns all information about the log data, and it contains two parameters, first is starting LSN and the second is ending LSN.
You can pass NULL for all the records.
|
1 2 3 4 |
SELECT last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = DB_ID() |
Using Restore with HeaderOnly option:
You can specify file name and path of the backup files, and it returns all information related to that backup file.
|
1 2 |
RESTORE HEADERONLY FROM DISK = 'C:\Temp\MyFull.bak' RESTORE HEADERONLY FROM DISK = 'C:\Temp\MyTran01.trn' |
Leave a Reply