DataBases Error log check and master is backed up every day at 1 am, so this should report success though job generate
declare @sql varchar(50)
declare @mins_before int
select @mins_before = 15
if object_id('tempdb..#errorlog') is not null
begin
drop table #errorlog
end
create table #errorlog
(
EntryTime datetime,
Source varchar(50),
LogEntry varchar(4000)
)
select @sql = 'exec master..sp_readerrorlog'
insert into #errorlog
execute (@sql)
if exists
(
select *
from #errorlog
where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before
and Source = 'backup'
and LogEntry like 'Database backed up. Database: master%'
)
and not exists
(
select *
from #errorlog
where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before
and Source = 'backup'
and LogEntry like 'Error%'
)
begin
select 'ok, we have a successful backup'
select *
from #errorlog
where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before
and Source = 'backup'
and LogEntry like 'Database backed up. Database: master%'
order by EntryTime
end
else
begin
select 'not ok, we do not have a successful backup'
select *
from #errorlog
where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before
order by EntryTime
declare @servername varchar(50)
declare @recipients varchar(200)
declare @subject varchar(100)
Select @servername = @@servername,
@recipients = 'XYZ@a.com',
@subject = @servername + ' – backup database failed'
exec msdb..sp_send_dbmail
@profile_name = @servername,
@recipients = @recipients,
@subject = @subject,
@body = @subject
end
if object_id('tempdb..#errorlog') is not null
begin
drop table #errorlog
end