This is an interesting requirement that many of the DBAs are asked. When was the last backup of their databases were taken. When was the transaction log backup was taken. Use this simple script that allows you to answer this question.

-- Backup of data
SELECT Database_Name, 
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup, 
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
ORDER BY 3 DESC
Go
-- Find the backup of Transaction Log files
SELECT Database_Name, 
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup, 
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'l'
GROUP BY Database_Name
ORDER BY 3 DESC

This will give you the list of all the databases with the last date when they were backedup.

Download the script (0.5 KB)