Help with script that mails the dba when db backup fails

• vaddi

  SSC Enthusiast

  Points: 173

  More actions

  August 31, 2006 at 2:12 pm

  #174139

  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 , 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

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  September 1, 2006 at 5:21 am

  #657983

  DECLARE @Date_Time_Requested  DATETIME

  DECLARE @Count_TimeOut_Records INT

  DECLARE @MailMessage  VARCHAR(8000)

  DECLARE @NewLine   CHAR(2)

  DECLARE @Oldest_Date  DATETIME

  DECLARE @EmailAddress  VARCHAR(50)

  DECLARE @MailSubject                VARCHAR(150)

  ---Initialisation

  SELECT @Oldest_Date = getdate()

  SELECT @Count_TimeOut_Records = 1

    Set @EmailAddress = 'yourmailaddress@yourprovider.whatever'

    SELECT @NewLine = char(13) + char(10)

    SELECT @MailSubject = 'TEST  !  ( ' + @@servername + ' ) '

    SELECT @MailMessage = 'TEST  ! ( ' + @@servername + ' ) ' + @Newline

    SELECT @MailMessage = @MailMessage + 'message via SQLMail.' + @Newline

    SELECT @MailMessage = @MailMessage + ' it is now ' + convert(varchar(30),@Oldest_Date,121) + '.'

                  print @MailMessage

     EXEC master..xp_sendmail @Recipients=@EmailAddress, @Message=@MailMessage, @Subject=@MailSubject

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me