Mailto_on warning and error?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Mailto_on warning and error?

Postby vad2319 » Tue May 08, 2012 9:57 pm

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.
vad2319
 
Posts: 25
Joined: Fri Dec 21, 2007 7:10 am

Postby petey » Wed May 09, 2012 1:40 am

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests