SQL Stuff

Find All Databases Without A Recent Backup

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())) 
Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s