Help - script that mails when DB backup fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Help - script that mails when DB backup fails

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-31 : 10:49:47

Hello

I have got a script that mails the dba mail box when the database backup fails. But I have to change the servernames and database names, wherever I implement on different databases. I would like to know how I can make it more dynamic , in the sense general so that it takes the parameters where ever I implement. The mail I would be getting should be like :

exec master.dbo.sendmail
@to = 'droche@depaul.edu,
@subject = 'MSSQL Database Backup Failure Notification',
@message ='Server name = %COMPUTERNAME% , Database Name Test Backup Failed' ;

So I should be able to pass the parameters servername and database name.

The script that I am using for the mailing is :

USE master;
GO
CREATE PROCEDURE dbo.SendMail
@to VARCHAR(255),
@subject VARCHAR(255),
@message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@rv INT,
@from VARCHAR(64),
@server VARCHAR(255);
SELECT
@from = 'testsql2000@is.depaul.edu',
@server = 'smtp.depaul.edu';
EXEC @rv = dbo.xp_smtp_sendmail
@to = @to,
@from = @from,
@message = @message,
@subject = @subject,
@server = @server;
END
GO

--- After the above script is run the following should be given in the 2nd step when
--- the backup jobs are scheduled ------

exec master.dbo.sendmail
@to = 'dvaddi@depaul.edu',
@subject =' Test sqlserver 2000',
@message ='Test Database Backup Failed' ;

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources