Check when every SQL Server database was last backed up

Why it matters

ERP systems such as Dynamics NAV, Sage and Tally usually sit on SQL Server. If a backup job silently fails, you only find out when you need a restore. Run this weekly.

Query

Run in SQL Server Management Studio against the instance:

SELECT d.name AS [Database],
       d.recovery_model_desc AS [Recovery model],
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS [Last full],
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS [Last differential],
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS [Last log]
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY [Last full];

Reading the results

• No full backup, or one older than a day or two, needs fixing first.
• Databases in FULL recovery need regular log backups, or the log file keeps growing.
• A backup only counts once you have tested a restore.