I am occasionally asked when the last time a particular database was backed up. In nearly all cases, the answer should always be, at the very least, “last night!” but there are always a few systems that don’t require the nightly backup. In any case, I’ve found it useful to script out a quick proc that I can run any time that question is asked.
SELECT db.name AS 'Database',
CASE WHEN MAX(b.backup_finish_date) IS NULL THEN 'No Backup'
END AS 'Last Backup Date'
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC
This script will return a list of all databases on the server along with their latest backup date. Its an easy way to keep an eye on all your backups if you’re not using any 3rd party software to track it.