Competition: What’s your favorite Redgate tool? Enter now.

Scheduled restore fails, SQL Agent reports success

steffansteffan Posts: 9
edited December 26, 2006 6:19PM in SQL Backup Previous Versions
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.

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
Sign In or Register to comment.