This site is currently using a generated translation

Quickly check the latest backup status of an instance

Here's a query that quickly lists all databases and when the last backup was made (full or diff), it also shows who made the backup if you happen to have clumsy users who are making backups themselves on a regular basis.

I use this either on a new server that we have taken over operation of or an existing one when our backup monitoring has sounded the alarm and I want to see in more detail what has happened, and afterwards to verify that everything is fixed if the backup has failed. I can quickly see if it's a single database (disk out on backup server?) or if all databases have not been backed up (sql agent not running?)

SELECT D.Name as DatabaseName,
    CONVERT( SmallDateTime , MAX(BS.backup_finish_date)) as Last_Backup,
    DATEDIFF(d, MAX(BS.backup_finish_date), Getdate()) as Days_Since_Last,
            COALESCE(Convert(varchar(32), MAX(BS.user_name), 101), 'NA') as UserName
FROM sys.sysdatabases D LEFT OUTER JOIN msdb.dbo.backupset BS
ON BS.database_name =
WHERE BS.type <> 'L' OR BS.type IS NULL --T2.Type = 'D' Or T2.Type = 'I'
HAVING D.Name <> 'tempdb'