I love the idea of the central monitoring server using linked servers....I use one too. Makes my life so easy.

I use a similar process but its a little less complicated. I just send an email if a backup has not been completed for more than 24 hours. justs sends a generic email if it has not. This script gets scheduled with the agent to run once a day. looks like this; (modifed to work in your enviroment)

declare @server_name nvarchar(250)

declare db_crsr_DBS cursor for

select server_name from [Monitordb].[dbo].[Remote_Databases]

open db_crsr_DBS

fetch next from db_crsr_DBS into @server_name

while @@fetch_status = 0

begin

DECLARE @cmd nvarchar(2000)

set @cmd = '

Select '''+@server_name+''' as ServerName, a.name as [DB_Name], backup_type, Backup_Date, getdate() as Date_Inserted

from ['+@server_name+'].[master].[dbo].[sysdatabases] a

left join

(select database_name, Backup_Type = Case type when ''D'' then ''Database''

When ''I'' then ''Database Differential''

When ''L'' then ''Log''

When ''F'' then ''File or Filegroup''

Else ''Error'' End,

max(backup_finish_date) backup_date

from ['+@server_name+'].[msdb].[dbo].[backupset] where backup_finish_date <= getdate()

group by database_name,Type ) B

on a.name=b.database_name

where a.name != ''tempdb'' and Backup_Date < dateADD(hh,-24,getdate()) and a.status not between 500 and 600

or Backup_date is null and a.name != ''tempdb'' and a.status not between 500 and 600'

create table #just_temporary (ServerName nvarchar(150), [DB_Name] nvarchar(150), backup_type nvarchar(25),

Backup_Date datetime, Date_Inserted datetime)

insert into #just_temporary

exec (@cmd)

if exists (select top 1 * from #just_temporary)

begin

declare @mailcmd nvarchar(2000)

SET @mailcmd = 'Select convert(nvarchar(30),a.name) as [DB_Name], backup_type, Backup_Date

from ['+@server_name+'].[master].[dbo].[sysdatabases] a

left join

(select database_name, Backup_Type = Case type when ''D'' then ''Database''

When ''I'' then ''Database Differential''

When ''L'' then ''Log''

When ''F'' then ''File or Filegroup''

Else ''Error'' End,

max(backup_finish_date) backup_date

from ['+@server_name+'].[msdb].[dbo].[backupset] where backup_finish_date <= getdate()

group by database_name,Type ) B

on a.name=b.database_name

where a.name != ''tempdb'' and Backup_Date < dateADD(hh,-24,getdate()) and a.status not between 500 and 600

or Backup_date is null and a.name != ''tempdb'' and a.status not between 500 and 600'

declare @mybody nvarchar (150)

set @mybody = 'The following databases require a backup on instance '+@server_name+';

'

declare @mysubject nvarchar(200)

set @mysubject = 'Missing backups on server '+@server_name+'.'

EXEC msdb.dbo.sp_send_dbmail @recipients='geoffa@geoffa.com',

@subject = @mysubject,

@body = @mybody,

@query = @mailcmd,

@query_result_header = 0,

@query_result_width = 600

end

drop table #just_temporary

fetch next from db_crsr_DBS into @server_name

end

close db_crsr_DBS

deallocate db_crsr_DBS

Thanks for sharing your process.