Backup Statistics and History - SQL Server

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at the other place and using for the database restore which used for reporting purposes or used in log shipping purpose. Because database backups are the most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loss.

You can read my earlier posts Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like to share the script which helps us to show the database backup status, history of theirs when they are done based on schedule, at where are taking and when, backup types, backups, physical device and the size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO
 
SELECT
 bs.server_name AS Server, -- Server name
 bs.database_name AS DatabseName , -- Database name
 CASE bs.compatibility_level 
   WHEN 80 THEN 'SQL Server 2000'
   WHEN 90 THEN 'SQL Server 2005 '
   WHEN 100 THEN 'SQL Server 2008'
   WHEN 110 THEN 'SQL Server 2012'
 END AS CompatibilityLevel , -- Return backup compatibility level
 recovery_model AS Recoverymodel , -- Database recovery model
 CASE bs.type 
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file'
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 CASE device_type
   WHEN 2 THEN 'Disk - Temporary'
   WHEN 102  THEN 'Disk - Permanent'
   WHEN 5  THEN 'Tape - Temporary'
   WHEN 105  THEN 'Tape - Temporary'
   ELSE 'Other Device'
 END AS DeviceType, -- Device type
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 bs.compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
 ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC
 
GO

(Click on image to enlarge)

Hope you liked this post.