sqlbackup restore verifyonly

lghollow1260lghollow1260 Posts: 4
edited December 17, 2007 10:39PM in SQL Backup Previous Versions
i am trying to verify that a backup is valid, my problem is i can't get the verify to return me a code to let me know if it failed.

below is a sample of the code, it's a dynamic statement...

how do i get the return codes back or at least be able to dump the data output to a table... the issue with the output is it returns 2 different layouts? anyway to suppress on of them so i can dump it out to table?


SET @sqlvcmd = 'EXEC [SNDVS007].master.dbo.sqlbackup ''-SQL
"RESTORE VERIFYONLY FROM DISK = [\\SNDVS007\nsmBACKUPS\DB\RG\SNDVS007\DBA_NEW\DATA\DBA_NEW_20071212221320.BAK] '+ '"'''+ ',@exitcode OUTPUT, @sqlerrorcode OUTPUT;'

PRINT @sqlvcmd

EXEC @ri = sp_executesql @sqlvcmd,N'@exitcode int OUTPUT,@sqlerrorcode int OUTPUT',@exitcode = @exitcode, @sqlerrorcode=@sqlerrorcode
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • lghollow1260lghollow1260 Posts: 4
    i think you totally missed what I was asking? anyone else got a clue?
    · Share on Twitter
  • lghollow1260lghollow1260 Posts: 4
    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...
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.