Home > Uncategorized > Database (Full, Diff & Transaction Log) Backup List

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

 

 

 

 

 

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: