What are the challenges you face when working across database platforms? Take the survey
Options

Mailto_on warning and error?

vad2319vad2319 Posts: 40
edited May 8, 2012 8:40PM in SQL Backup Previous Versions
I have a log shipping job setup using SQL BACKUP 6.5 and it send a log file every 15 minutes.

The log shipping process works fine but yesterday the server went down for 3 hours and we still did not receive any emails. Is it not supposed to send us an email notifying us that the machine is unreachable?

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup '-SQL "RESTORE LOG [DBname] FROM DISK = ''\\server1\redgate\dbname\LOG_*.sqb'' WITH ERASEFILES = 15, FILEOPTIONS = 1, MAILTO_ONERRORONLY = ''john@yahoo.com'', STANDBY = ''D:\logs\dbname\UNDO_dbname.dat'', MOVETO = ''D:\logs\dbname''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END

We do receive emails on some of the other alerts. Do we need to change something in the above script to receive warnings and errors?

Thanks.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    The log shipping job is ran as a SQL Server Agent job, which has to connect to the SQL Server instance first to pass the restore command to SQL Backup.

    If your SQL Server instance was unavailable, SQL Backup did not have a chance to process the request, hence why no e-mail was sent out. In these cases, you would need to set up alert notifications on the SQL Server Agent job itself.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.