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

PowerShell and DB restore exitcode management with sqlcmd

Emed25Emed25 Posts: 3
edited August 14, 2014 9:31AM in SQL Backup Previous Versions
Greetings,

I am using a scripted process for automated individual SQL database restorations, from RedGate .SQB backup files, as needed. I am invoking sqlcmd via PowerShell, which performs the restoration by using the following example syntax:
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [DB_NAME] FROM DISK = ''FULL_PATH'' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE"'

This is simple enough and works, however, I did want to also incorporate some sort of error handling, based on the exitcode generated by the proc being called. I've tried using PowerShell's own $LASTEXITCODE, however, the code generated always appears to be 0, since the script and sqlcmd itself -- technically -- ran successfully, so then it does me no good. The actual first exitcode generated by the sqlbackup proc is ignored.

Might anyone have a method for exitcode handling, in combination with PowerShell, sqlcmd, and the sqlbackup stored procedure? Thanks in advance!

Comments

  • Options
    I believe I figured it out, after spending a lot more time with this. I'll post what I came up with here, just in case it helps someone else in the future:

    1. Added the -b switch when calling sqlcmd, which forces to terminate the batch if there is an error.

    2. Modified the restore script so that if the exitcode and sqlerrorcode are anything but 0, it will go to RAISERROR within the script (exact message does not appear to matter). Fortunately, the RedGate sqlbackup proc does allow for the exitcode and sqlerrorcode to be generated into a declared value, as such:
    DECLARE @exitcode INT
    DECLARE @sqlerrorcode INT
    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [DB_NAME] FROM DISK = ''BACKUP_FILE_PATH'' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

    3. Once sqlcmd processes RAISERROR in a script, with the -b switch, the $LASTEXITCODE in PowerShell appears to go from 0 to 1, which indicates a restore error. I can then have PowerShell stop the process there, or run an alternate process, which is what I needed.
Sign In or Register to comment.