Database (Full, Diff & Transaction Log) Backup List

I thought of writing about the subject after visiting a customer. It is really important to have the list of fullback and transaction log back sequentially performed. When it comes to Recovery I have seen most of the DBA running queries on msdb database and struggling to get the list of Backups and sequential Transaction Log Backup.

I have written T-SQL Code and designed a Report which helped me and other DBA to quickly check the backup and transaction log backups performed on a specific Database. This report was designed for one of our mission critical Database Only. Users are allowed to change the code according to their environment.

Snapshot of the Report is as below.

 

Script

SELECT bs.backup_set_id,bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,

            bs.database_creation_date,bs.backup_start_date,bs.backup_finish_date,

                type= CASE bs.type
WHEN
‘D’
THEN
‘FULL Backup’
WHEN
‘I’
THEN
‘DIFF Backup’
WHEN
‘L’
THEN
‘LOG Backup’
END ,bs.database_name,bmf.media_family_id,bmf.physical_device_name

                    FROM msdb.dbo.backupset bs INNER
JOIN msdb.dbo.backupmediafamily bmf ON

                bs.media_set_id=bmf.media_set_id

            and bs.database_name=‘DatabaseName’ and bs.backup_finish_date between
convert(nvarchar(30),GETDATE(),101)
and GETDATE()


ORDER
BY bs.backup_set_id asc

 

 

 

 

 

Leave a Reply

Your email address will not be published.