Finding the Latest Backup Timestamps for Your Databases

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.