Show the backup history of all databases

20 Aug 2019 0 minutes to read Contributors

Show the backup history of all databases on the server.


SELECT
T1.name ,T3_full.full_backup_start_date ,T3_full.full_backup_finish_date ,T3_full.full_Duration ,t3_full.full_backup_size ,t3_full.full_physical_device_name ,T3_diff.diff_backup_start_date ,T3_diff.diff_backup_finish_date ,T3_diff.diff_Duration ,t3_diff.diff_backup_size ,t3_diff.diff_physical_device_name ,T3_log.log_backup_start_date ,T3_log.log_backup_finish_date ,T3_log.log_Duration ,t3_log.log_backup_size ,t3_log.log_physical_device_name FROM master..sysdatabases T1 LEFT OUTER JOIN ( SELECT database_name ,MAX(full_backup_start_date) AS full_backup_start_date ,MAX(full_backup_finish_date) AS full_backup_finish_date ,MAX(diff_backup_start_date) AS diff_backup_start_date ,MAX(diff_backup_finish_date) AS diff_backup_finish_date ,MAX(log_backup_start_date) AS log_backup_start_date ,MAX(log_backup_finish_date) AS log_backup_finish_date FROM ( SELECT msdb.dbo.backupset.database_name ,CASE WHEN msdb.dbo.backupset.type = 'D' THEN MAX(msdb.dbo.backupset.backup_start_date) ELSE NULL END AS full_backup_start_date ,CASE WHEN msdb.dbo.backupset.type = 'D' THEN MAX(msdb.dbo.backupset.backup_finish_date) ELSE NULL END AS full_backup_finish_date ,CASE WHEN msdb.dbo.backupset.type = 'I' THEN MAX(msdb.dbo.backupset.backup_start_date) ELSE NULL END AS diff_backup_start_date ,CASE WHEN msdb.dbo.backupset.type = 'I' THEN MAX(msdb.dbo.backupset.backup_finish_date) ELSE NULL END AS diff_backup_finish_date ,CASE WHEN msdb.dbo.backupset.type = 'L' THEN MAX(msdb.dbo.backupset.backup_start_date) ELSE NULL END AS log_backup_start_date ,CASE WHEN msdb.dbo.backupset.type = 'L' THEN MAX(msdb.dbo.backupset.backup_finish_date) ELSE NULL END AS log_backup_finish_date FROM msdb.dbo.backupset GROUP BY msdb.dbo.backupset.database_name ,msdb.dbo.backupset.type ) max_date_subset GROUP BY database_name ) T2 ON T1.name = T2.database_name LEFT OUTER JOIN ( SELECT msdb.dbo.backupset.database_name ,msdb.dbo.backupset.backup_start_date AS full_backup_start_date ,msdb.dbo.backupset.backup_finish_date AS full_backup_finish_date ,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS full_Duration ,msdb.dbo.backupset.backup_size AS full_backup_size ,msdb.dbo.backupmediafamily.physical_device_name AS full_physical_device_name FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id ) T3_full ON T2.database_name = T3_full.database_name AND t2.full_backup_start_date = T3_full.full_backup_start_date AND t2.full_backup_finish_date = T3_full.full_backup_finish_date LEFT OUTER JOIN ( SELECT msdb.dbo.backupset.database_name ,msdb.dbo.backupset.backup_start_date AS diff_backup_start_date ,msdb.dbo.backupset.backup_finish_date AS diff_backup_finish_date ,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS diff_Duration ,msdb.dbo.backupset.backup_size AS diff_backup_size ,msdb.dbo.backupmediafamily.physical_device_name AS diff_physical_device_name FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id ) T3_diff ON T2.database_name = T3_diff.database_name AND t2.diff_backup_start_date = T3_diff.diff_backup_start_date AND t2.diff_backup_finish_date = T3_diff.diff_backup_finish_date LEFT OUTER JOIN ( SELECT msdb.dbo.backupset.database_name ,msdb.dbo.backupset.backup_start_date AS log_backup_start_date ,msdb.dbo.backupset.backup_finish_date AS log_backup_finish_date ,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS log_Duration ,msdb.dbo.backupset.backup_size AS log_backup_size ,msdb.dbo.backupmediafamily.physical_device_name AS log_physical_device_name FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id ) T3_log ON T2.database_name = T3_log.database_name AND t2.log_backup_start_date = T3_log.log_backup_start_date AND t2.log_backup_finish_date = T3_log.log_backup_finish_date

In this article