Scheduled restore fails, SQL Agent reports success

steffan

Any thoughts on how I could get the SQLAgent job to report failure when a scheduled restore fails? Even though the SQLbackup step failed, the job continued on to the next step because it thought the step succeeded. I know what caused the error, and don't anticipate it happening again, but would like to get notified if it does fail.

Thanks
-S

(Step output details below)



Executed as user: RTPREPORT\Administrator. SQL Backup v4.6.0.815

---

Restoring rtpx2REPORT (database) from:
\\10.100.1.55\RTPshare\RTPx2.sqb

Error 580: Failed to open file. Message: (Cannot open file "\\10.100.1.55\RTPshare\RTPx2.sqb".
The process cannot access the file because it is being used by another process.)

SQL Backup exit code: 580
name value. The step succeeded.

---

petey

E.g.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES [d1]  TO DISK = ''E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
  RAISERROR ('SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

This will alert you to all warnings and errors. If you want to ignore warnings, then ignore all exitcodes less than 500.

steffan

Thanks... I'll give that a whirl. Also just realized that I can use the "MAILTO_ONERROR" syntax on a restore job, even though the GUI doesn't offer it.

Thanks
-S