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

Programatically verify Backups run in SQLAgent

UrsusHorribilisUrsusHorribilis Posts: 22
edited October 17, 2007 8:11AM in SQL Backup Previous Versions
The scenario: We run nightly backups from a SQLAgent Job & have experienced failures for various reasons.

The problem: At times, even though we don't get a valid backup, the Job Step reports success since the XP ran successfully.

The solution?: What I'd like to do is verify the backup "programatically" in that, I'd like to report failure to the Job when the backup is not valid. I was thinking initially of using the RESTORE VERIFYONLY or RESTORE SQBHEADERONLY syntax. The problem I'm runnning into is that since both return 2 datasets with different #'s of columns I can't parse or insert to a temp table & parse. Any help would be much appreciated. Thanks

Comments

  • Options
    peteypetey Posts: 2,358 New member
    You could set up SQL Backup to verify the backup using the VERIFY option after its been created e.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH VERIFY" '...
    
    If you want to manually perform the verification, you can catch any resulting errors in one or both of the output parameters, and raise an error accordingly in the job step e.g.
    DECLARE @exitcode int 
    DECLARE @sqlerrorcode int 
    
    EXEC master..sqlbackup N'-SQL "RESTORE VERIFYONLY ..." ',  @exitcode OUTPUT, @sqlerrorcode OUTPUT 
    
    IF (@exitcode <>0) OR (@sqlerrorcode <> 0) 
    BEGIN 
      RAISERROR ('SQL Backup verification failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) 
    END
    
    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.