Post reply

using sp_executesql and restore verifyonly

  • May 25, 2007 at 12:22 pm

    #119098

    Trying to get output form restore verifyonly command using sp_executesql with no luck. Have tried several variations of the following, but can't get the output in comments.

    declare @backupdevice varchar(75),

               @verifystatement nvarchar(200), 

               @comments varchar(100), 

               @param nvarchar(200)

    set @verifystatement = N'restore verifyonly from disk = ''C:\SQL-Backups\PBC\PBC_db_200705242245.BAK'''

    select @verifystatement

    set @param = N'@comments varchar(100) OUTPUT'

    select @param

    exec dbo.sp_executesql @verifystatement, @param, @comments OUTPUT

    select @comments

    Any suggestions are greatly appreciated.

    I don't know if I'm posting in the right place, so please let me know if I should move somewhere else.

    Thanks, Megan

  • May 25, 2007 at 12:53 pm

    #709074

    I don't believe you can get anything back because "restore" does not return a result set.  It outputs a "message" only which is neither a "Return Value/Code" or "Result Set" that can be captured.

    The closest way would be to run the command through one of the command line interfaces and capture the output to a text file.

    I'll admit I could be mistaken here, but I really don't think it is possible through sp_executesql running a "restore" command.

    James.

  • May 25, 2007 at 12:58 pm

    #709075

    What is the error message that you are getting when executing this statement?

  • May 25, 2007 at 1:01 pm

    #709077

    Thanks James, that makes sense.

    FYI - no error code, just doesn't return anything in @comments.

    Thanks.

  • May 25, 2007 at 1:08 pm

    #709079

    I'm currently working on the RESTORE VERIFYONLY functionality. Trying to write the output of the RESTORE VERFIYONLY onto the Error Log. Managed to successfully do it...but it works only for Error_Severity > 10. Let me know if you are looking for something similar !!

     

  • May 25, 2007 at 2:01 pm

    #709091

    If you are simply looking to determine whether the verification failed, you don't need to get anything back from dynamic SQL, you get something by default as the @@ERROR variable will be set.  Simply check @@ERROR to know if verification failed.  See the following code snipit for an example:

       SET  @DynamicSQL = 'RESTORE  VERIFYONLY' + @CRLF

       SET  @DynamicSQL = @DynamicSQL + 'FROM  DISK = @BackupFile' + @CRLF

       SET  @DynamicSQL = @DynamicSQL + 'WITH  PASSWORD = @Password' + @CRLF

       EXECUTE  sp_executesql 

         @DynamicSQL,

         N'@BackupFile VARCHAR(4000), @Password SYSNAME',

         @BackupFile,

         @Password

       IF @@ERROR <> 0

       BEGIN

        SET  @EmailMessage = '<font color = ''red''>The backup of database ' + @DatabaseName + ' failed verification.</font><br> '

        SET  @EmailMessage = @EmailMessage + 'The name of the backup file is "' + @BackupFile + '".'

       END

  • May 30, 2007 at 9:11 am

    #709711

    Thanks Ed. That helps.

    -Megan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply