Find databases that haven’t had a backup in over 1 day. ( 24 hours ). Here’s some quick sql logic that will help you get that info. This logic assumes the databases that have been backed up at least once. Basically; they require an entry in the msdb..backupset table.
use master; set nocount on select 'server' = upper(@@servername) , 'database' = upper(bs.database_name) , 'last backup' = cast (datediff(day, max(bs.backup_finish_date), getdate()) as varchar(10)) + ' Days Old' , 'time of backup' = replace(replace(replace(left(max(bs.backup_finish_date), 19),':', '-'), 'AM', 'am'), 'PM', 'pm') + ' ' + datename(dw, max(bs.backup_finish_date)) from msdb.dbo.backupset bs where type = 'd' group by bs.database_name having (max(bs.backup_finish_date) < dateadd(hour, -24, getdate()))
Categories: SQL Stuff