PowerShell and DB restore exitcode management with sqlcmd
Emed25
Posts: 3
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:
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!
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
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:
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.