Would you like to know when any of your SQL backups were last backed up? Or did you want to verify that a back up actually ran? Why now you can!
select
max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Days since backup',
b.backup_size,
d.name as database_name
from
master..sysdatabases d with (nolock)
left join
msdb..backupset b with (nolock)
on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from
msdb..backupset b2
where
b.database_name = b2.database_name
and b2.type = 'D')
where
d.name != 'tempdb'
group by
d.name, b.type, b.backup_size
This produces the following:-
