Programatically verify Backups run in SQLAgent

UrsusHorribilis

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

petey

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

UrsusHorribilis

Thanks