SELECT @@servername AS server_name, s.name AS database_name, backup_type =
CASE
    WHEN b.type = 'D' THEN 'Full'
    WHEN b.type = 'I' THEN 'Diff'
END,
MAX(b.backup_start_date) AS last_backup_start_time,
DATEDIFF(d, MAX(b.backup_start_date), getdate()) AS days_since_last_backup, STATUS =
CASE
    WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) <= 7 THEN 'OK' /*if the most recent full backup was less than or equal to 7 days ago, we're OK*/     WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) > 7 THEN 'Possible Problem' /*if the most recent full backup was more than 7 days ago, Possible Problem*/
    WHEN b.type = 'I' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) <= 2 THEN 'OK' /*if the most recent diff backup was less than or equal to 3 days ago, we're OK*/     WHEN b.type = 'I' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) > 2 THEN 'Possible Problem' /*if the most recent diff backup was more than 3 days ago, Possible Problem*/
    WHEN MAX(b.backup_start_date) IS NULL THEN 'No Backup Exists'
END
FROM sys.DATABASES s LEFT JOIN msdb..backupset b
ON s.name = b.database_name
WHERE s.name <> 'master' AND s.name <> 'model' AND s.name <> 'msdb' AND s.name <> 'tempdb'
GROUP BY s.name, b.type
ORDER BY s.name