-- GET DATABASES WITHOUT FULL OR TLOG BACKUPS
-- dbs with no full backups in last week
(select cast(a.[name] as varchar(100)) as db_nm into #no_full_backup
from master.dbo.sysdatabases a
left join msdb.dbo.backupset b
on a.[name] = b.database_name and datediff(hour,b.backup_finish_date,getdate())<168 -- 1 week
and b.type='D'
where a.[name] != 'tempdb'
and databasepropertyex(a.[name], 'isinstandby') = 0
and databasepropertyex(a.[name], 'status') = 'online'
and databasepropertyex(a.[name], 'updateability') = 'read_write'
and b.database_name is null )
-- dbs with no transaction log backup for a day
(select cast(a.database_name as varchar(100)) as db_nm into #no_tlog_backup
from
(
select [name] as database_name
from master.dbo.sysdatabases
where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED')
and databasepropertyex([name], 'isinstandby') = 0
and databasepropertyex([name], 'status') = 'online'
and databasepropertyex([name], 'updateability') = 'read_write'
and [name] not in ('model','tempdb')
) a
left join msdb.dbo.backupset b
on a.database_name = b.database_name and b.type='L' and datediff(hour,b.backup_finish_date,getdate())<24
where b.database_name is null)
--select * from #no_full_backup
--select * from #no_tlog_backup
if (select count(*) from #no_full_backup) > 0
raiserror ('databases with no full backups in last 7 days exist',16,1) with log
if (select count(*) from #no_tlog_backup) > 0
raiserror ('databases with no transaction backups in last 1 days exist',16,1) with log
drop table #no_full_backup
drop table #no_tlog_backup
This one is actually SQL2000 compatible but works on other versions. You would be well to get the database status values from table master.sys.databases
---------------------------------------------------------------------