sqlbackup restore verifyonly

• 
  You can check the exitcode and sqlerrorcode values for warnings and errors. E.g.
  

  DECLARE @exitcode INT
  DECLARE @sqlerrorcode INT
  
  EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = [\\SNDVS007\nsmBACKUPS\DB\RG\SNDVS007\DBA_NEW\DATA\DBA_NEW_20071212221320.BAK"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
  
  IF (@exitcode > 0 AND @exitcode < 500) AND @sqlerrorcode = 0
  	PRINT 'Verification completed with warnings: exitcode ' + CAST(@exitcode AS VARCHAR(32))
  ELSE IF @exitcode >= 500 OR @sqlerrorcode > 0
  	PRINT 'Verification completed with errors: exitcode ' + CAST(@exitcode AS VARCHAR(32)) + ', sqlerrorcode ' + CAST(@sqlerrorcode AS VARCHAR(32))
  ELSE
  	PRINT 'Verification completed successfully'
  

  
  To be able to store the output in a table, use the SINGLERESULTSET option e.g.
  

  CREATE TABLE #temp1 (col1 nvarchar(256)) 
  INSERT INTO #temp1 EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [e:\temp\pubs.sqb] WITH SINGLERESULTSET"' 
  SELECT * FROM #temp1 
  DROP TABLE #temp1
  

  petey December 16, 2007 20:55

  - 0 +
• 
  i think you totally missed what I was asking? anyone else got a clue?

  lghollow1260 December 17, 2007 09:02

  - 0 +
• 
  not exactly my idea way of doing it but with v5.2 i am able to return a single output from the verify and then parse thru the rows returned to get the error code. There needs to be a better way of returning error codes than forced to interrogate a result set. but at least this work for now...

  lghollow1260 December 17, 2007 18:57

  - 0 +
• 
  When you run the sqlbackup extended stored procedure, it returns an exit code and a SQL error code if it is provided with 2 output parameters. The exit code is SQL Backup specific, where a value less than 500 indicates warnings, while values of 500 or more indicates errors. A description of each code is provided in the help file. The SQL error code is the error code returned by SQL Server itself.
  
  '...a better way of returning error codes than forced to interrogate a result set...' is to use these codes. First, declare the 2 variables:
  

  DECLARE @exitcode INT
  DECLARE @sqlerrorcode INT
  

  Then run the extended stored procedure using the above 2 variables as output parameters:
  

  EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY ..." ', @exitcode OUTPUT, @sqlerrorcode OUTPUT
  

  Then check the values of these two variables and do whatever you need, e.g.:
  

  IF @exitcode <> 0 OR @sqlerrorcode <> 0
  BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) 
  END
  

  Is this what you are after? If not, could you please clarify your intentions? Thank you.

  petey December 17, 2007 22:39

  - 0 +

Add comment

Please sign in to leave a comment.