I wanted to find out the most recent date and time each database was last backed up, focusing specifically on full, differential, and transaction log backups, all displayed on a single line for each database for easier viewing and reading. For example:
This provides a high-level overview of when my databases were last backed up, including differential and transaction log backups, if applicable. The query results also show the duration of each backup (not shown in the above screenshot). Here is the query:
USE msdb
GO
;WITH cte_latest_backup AS (
SELECT
sd.Name AS DatabaseName,
bs.type AS backup_type,
MAX(bs.backup_start_date) AS backup_start_time,
MAX(bs.backup_finish_date) AS backup_finish_time
FROM sys.sysdatabases sd
INNER JOIN msdb.dbo.backupset bs
ON bs.database_name = sd.name
GROUP BY sd.Name, bs.type
),
cte_full_backup AS
(
SELECT * FROM cte_latest_backup
WHERE backup_type = 'D'
),
cte_diff_backup AS
(
SELECT * FROM cte_latest_backup
WHERE backup_type = 'I'
),
cte_tlog_backup AS
(
SELECT * FROM cte_latest_backup
WHERE backup_type = 'L'
)
SELECT
d.name AS [Database],
d.recovery_model_desc,
full_backup.backup_start_time AS full_backup_start,
diff_backup.backup_start_time AS diff_backup_start,
tlog_backup.backup_start_time AS tlog_backup_start,
DATEDIFF(MINUTE, full_backup.backup_start_time,
full_backup.backup_finish_time) AS full_backup_elapsed_min,
DATEDIFF(MINUTE, diff_backup.backup_start_time,
diff_backup.backup_finish_time) AS diff_backup_elapsed_min,
DATEDIFF(SECOND, tlog_backup.backup_start_time,
tlog_backup.backup_finish_time) AS tlog_backup_elapsed_sec
FROM master.sys.databases d
LEFT JOIN cte_full_backup full_backup
ON full_backup.DatabaseName = d.name
LEFT JOIN cte_diff_backup diff_backup
ON diff_backup.DatabaseName = d.name
LEFT JOIN cte_tlog_backup tlog_backup
ON tlog_backup.DatabaseName = d.name
WHERE d.name NOT IN ('tempdb')
ORDER BY [Database]
Gotchas:
- The query doesn't capture other available types of backups like file backup, file group backups, partial backups etc. If your backup strategy includes any of those backup types, please consider adapting the query to meet your needs.
- The query doesn't also differentiate whether the full backup was done in copy-only mode or not.
- In AlwaysOn AG servers, some of your database backups maybe offloaded or distributed across primary and secondary replicas. This query doesn't capture backups performed on other replicas and therefore the most recent backup date/times displayed by the query may not be accurate for databases that are in an Availability Group.