y6TxNb50

1declare @xml nvarchar(max)
2declare @returnvalue  nvarchar(max)
3declare @body nvarchar(max)
4
5 if exists (select 'x' from sysobjects where name ='temp_tbl')
6begin
7       drop table temp_tbl
8end
9
10select database_name,type,max(backup_start_date) backupdate
11into temp_tbl from msdb.dbo.backupset
12where database_name in 
13(select name from sys.databases where recovery_model_desc='FULL')
14group by  database_name,type
15order by  database_name,type
16
17if exists (select 'x' from sysobjects where name ='final_table')
18begin
19       drop table final_table
20end
21
22create table final_table(DBName nvarchar(500),FullBackup_date datetime,LogBackupDate datetime)
23insert into final_table(DBName) 
24selecT distinct Database_name From temp_tbl
25
26update x
27set x.FullBackup_date=y.backupdate
28from final_table x,temp_tbl y
29where x.DBname=y.database_name 
30and y.type='D'
31
32update x
33set x.LogBackupDate=y.backupdate
34from final_table x,temp_tbl y
35where x.DBname=y.database_name 
36and y.type='L'
37
38
39IF (select count(*)  FROM final_table) >0 
40begin
41EXEC msdb.dbo.sp_send_dbmail 
42@profile_name = 'XXX',
43    @recipients = 'SSSS@at.com',
44    @subject = N'Databases not backed for last 2 hrs',
45    @query='select * FROM final_table where LogBackupDate < DATEADD(Hour,-2,getdate()) '
46   END