- Docs
- /
06 Jun 2022 24592 views 0 minutes to read Contributors
Show the backup history of all databases on the server.
T-SQLTransact-SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899 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_nameFROM master..sysdatabases T1LEFT 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_dateFROM (SELECT msdb.dbo.backupset.database_name,CASEWHEN msdb.dbo.backupset.type = 'D'THEN MAX(msdb.dbo.backupset.backup_start_date)ELSE NULLEND AS full_backup_start_date,CASEWHEN msdb.dbo.backupset.type = 'D'THEN MAX(msdb.dbo.backupset.backup_finish_date)ELSE NULLEND AS full_backup_finish_date,CASEWHEN msdb.dbo.backupset.type = 'I'THEN MAX(msdb.dbo.backupset.backup_start_date)ELSE NULLEND AS diff_backup_start_date,CASEWHEN msdb.dbo.backupset.type = 'I'THEN MAX(msdb.dbo.backupset.backup_finish_date)ELSE NULLEND AS diff_backup_finish_date,CASEWHEN msdb.dbo.backupset.type = 'L'THEN MAX(msdb.dbo.backupset.backup_start_date)ELSE NULLEND AS log_backup_start_date,CASEWHEN msdb.dbo.backupset.type = 'L'THEN MAX(msdb.dbo.backupset.backup_finish_date)ELSE NULLEND AS log_backup_finish_dateFROM msdb.dbo.backupsetGROUP BY msdb.dbo.backupset.database_name,msdb.dbo.backupset.type) max_date_subsetGROUP BY database_name) T2 ON T1.name = T2.database_nameLEFT 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_nameFROM msdb.dbo.backupmediafamilyINNER 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_nameAND t2.full_backup_start_date = T3_full.full_backup_start_dateAND t2.full_backup_finish_date = T3_full.full_backup_finish_dateLEFT 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_nameFROM msdb.dbo.backupmediafamilyINNER 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_nameAND t2.diff_backup_start_date = T3_diff.diff_backup_start_dateAND t2.diff_backup_finish_date = T3_diff.diff_backup_finish_dateLEFT 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_nameFROM msdb.dbo.backupmediafamilyINNER 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_nameAND t2.log_backup_start_date = T3_log.log_backup_start_dateAND t2.log_backup_finish_date = T3_log.log_backup_finish_date
In this article
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly