Check Full Recovery DBs Have Tran Log Backups

create procedure check_full_recovery_databases @num_of_days int =7
as

-- quick procedure to see which full recovery databases haven't had tran log backups 
-- within the number of days passed in

-- this procedure has been tested on sql server 2000 sp4 and SQL Server 2005
-- any problems email pgr_consulting @ yahoo.com

create table #full_recovery_databases (dbid int identity(1,1), dbname varchar(50))

insert into #full_recovery_databases
select name
from sysdatabases
where convert(varchar(50),DatabasePropertyEx(name,'Recovery'))= 'FULL'

select 'the ' + dbname + ' database is set to Full recovery but has not had a Log backup in last ' + 
   cast(@num_of_days as varchar(3)) + ' days' 
as 'Full recovery mode and Log Backups check'
from #full_recovery_databases where dbname not in 
(select database_name from msdb..backupset 
 where type = 'L' 
 and backup_start_date > dateadd(day,-@num_of_days,getdate()))
order by dbname

drop table #full_recovery_databases
go