How to: Check Databases That Have Not Had a Full Backup in x Days

Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all.

DECLARE @num_of_days INT
SET @num_of_days = 1 

SELECT
	A.name
	, MAX(B.backup_finish_date) AS 'LastBackupDateTime'
FROM
	master.dbo.sysdatabases A WITH(NOLOCK) LEFT OUTER JOIN
	msdb.dbo.backupset B WITH(NOLOCK) ON A.name = B.database_name
WHERE (B.TYPE = 'D' OR B.TYPE IS NULL)
GROUP BY A.name
HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_date) IS NULL) 
ORDER BY A.name

Reference :
SQL DBs That Have Not Had A Full Backup in X Days