T-SQL – Monitoring Database Backup Status

T-SQL – Monitoring Database Backup Status

Being an SQL Server DBA, it is very important to retrieve Database Backup History, to make sure all backups are successful and there are no risk of losing critical data.

If you want to track down Backup Start date, finish date, backup size and etc. details, then below script would be a great help.

SELECT

  CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

  msdb.dbo.backupset.database_name,

  msdb.dbo.backupset.backup_start_date,

  msdb.dbo.backupset.backup_finish_date,

  msdb.dbo.backupset.expiration_date,

  CASE msdb..backupset.type

    WHEN ‘D’ THEN ‘Database’

    WHEN ‘L’ THEN ‘Log’

  END AS backup_type,

  msdb.dbo.backupset.backup_size,

  msdb.dbo.backupmediafamily.logical_device_name,

  msdb.dbo.backupmediafamily.physical_device_name,

  msdb.dbo.backupset.name AS backupset_name,

  msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily


INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102>= GETDATE()  7)

ORDER BY

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_finish_date

 

Please Note:
Above script pull backup inforation of last 7 days, if you want to modify the days range then edit the highlighted number.

Advertisements

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