-- 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

CREATE PROCEDURE usp_NoTlogBackup (@Length INT)
AS

DECLARE @BadDatabases VARCHAR(8000)
DECLARE @Body VARCHAR(8000)

SELECT @BadDatabases = STUFF(
(select ', ' + cast(a.database_name as varchar(100))
	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()) < @Length
	where b.database_name is null
FOR XML PATH ('')
),1,2,'')

IF (@BadDatabases IS NOT NULL)
BEGIN

SET @Body = 'On ' + @@SERVERNAME + ' The following Databases do not have Tlog backups: ' + @BadDatabases

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name',
                             @recipients = 'your@email.com',
                             @from_address = 'ServerName@from.com',
                             @body = @Body
END