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