Script when database backup fails

Question

Script when database backup fails

vaddi

SSC Veteran

Points: 223
More actions
August 28, 2006 at 9:14 am

#114623

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

Hello -

Maybe I misunderstand the issue, and I have made the assumption that the backup is a scheduled job, why not query the sysjobhistory or similar?

-- Cory

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

For that matter, why not just have SQL Server Agent notify you when the job fails? See "Notifications" in the job properties.

Greg

vaddi SSC Veteran Points: 223 More actions · Answer 3

But for the sqlserver agent to be used, I need to set up the sqlmail which requires the exchange on the server which has some problems. So I have been using the SMTP server.

Thanks

barbara johnson Mr or Mrs. 500 Points: 507 More actions · Answer 4

There is an article that describes how to send email messages without using SQL Mail. Refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech.

I utilize a version of the stored procedure given in the above article. You can search the web and find other similar articles. I have my backup jobs set up to notify the DBAs when the backup step fails.