Script when database backup fails

Hello,

I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.

I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @.message = @.message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.message = @.message + @.dbname + ' backup up on ' + @.Status + Char(13)
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

print @.message

EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of sqlserver!',
@.type = N'text/html',
@.message = @.message

Thanks

How are you performing the backup? If you are using say SQLAgent jobs then why don't you just include an additional step at the end after the backup step which fires only if the backup fails. This can email the details and you don't even have to use extended SPs etc. I believe that SQLAgent has ways to send emails. Otherwise, you will have to check for status of backup also in the msdb tables since you cannot create triggers on system tables.