August 28, 2006 at 9:14 am
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
August 28, 2006 at 9:29 am
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
August 28, 2006 at 9:54 am
For that matter, why not just have SQL Server Agent notify you when the job fails? See "Notifications" in the job properties.
Greg
Greg
August 28, 2006 at 9:59 am
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
August 29, 2006 at 10:22 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy