Database Backup History From MSDB in a pivot table
- by steveh99999
I knocked up a nice little query to display backup history for each database in a pivot table format.I wanted to display the most recent full, differential, and transaction log backup for each database. Here's the SQL :-WITH backupCTE AS (SELECT name, recovery_model_desc, d AS 'Last Full Backup', i AS 'Last Differential Backup', l AS 'Last Tlog Backup' FROM
( SELECT db.name, db.recovery_model_desc,type, backup_finish_date
FROM master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a
ON a.database_name = db.name
WHERE db.state_desc = 'ONLINE'
) AS Sourcetable
PIVOT
(MAX (backup_finish_date) FOR type IN (D,I,L) ) AS MostRecentBackup )
SELECT * FROM backupCTE Gives output such as this :- With this query, I can then build up some straightforward queries to ensure backups are scheduled and running as expected -For example, the following logic can be used ;- - WHERE [Last Full Backup] IS NULL) - ie database has never been backed up..
- WHERE [Last Tlog Backup] < DATEDIFF(mm,GETDATE(),-60) AND recovery_model_desc <> 'SIMPLE') - transction log not backed up in last 60 minutes.
- WHERE [Last Full Backup] < DATEDIFF(dd,GETDATE(),-1) AND [Last Differential Backup] < [Last Full Backup]) -- no backup in last day.- WHERE [Last Differential Backup] < DATEDIFF(dd,GETDATE(),-1) AND [Last Full Backup] < DATEDIFF(dd,GETDATE(),-8) ) -- no differential backup in last day when last full backup is over 8 days old.
