powershellpowershell-2.0

PowerShell - Nesting Try/Catch/Finally Commands


I'm trying to write a piece of code to drop and re-create an MS SQL Database in PowerShell using some Invoke-Sqlcmd commands (caused by an error in our systems). I wanted to build some contingency into this in case another program on the target machine is accessing the Database at the time that the drop command is issued. I thought that a good way to do this would be by nesting one Try/Catch/Finally command inside another, like so;

$strDbName= database
$strUsername= user
$strPassword= pass
$strmdfFilePath= "C:\foo.mdf"
$strldfFilePath= "C:\bar.ldf"

Try
    {
    Write-Host "INFO: Attempting Database DROP command..."
    Invoke-SqlCmd -Username "$strUserName" -Password "$strPassword" -Query "DROP database [$strDbName];"
    }
Catch
    {
    Try
        {
        Invoke-SqlCmd -Username "$strUserName" -Password "$strPassword" -Query "ALTER database [$strDbName] set offline with ROLLBACK IMMEDIATE;"
        Invoke-SqlCmd -Username "$strUserName" -Password "$strPassword" -Query "DROP database [$strDbName];"
    }
    Catch
        {
        Write-Host "Error message"
        }
    Finally
        {
        Exit
        }
    }
Finally
    {
    Invoke-SqlCmd -Username "$strUserName" -Password "$strPassword" -Query "CREATE DATABASE [$strDbName] ON (FILENAME = '$dirMdfFilePath'),(FILENAME = '$dirLdfFilePath') for ATTACH;"
    }

Two questions - A) Does nesting Try/Catch/Finally commands actually work? and B) Is this type of command sequence good practice? I don't have a test machine to try this out on and if there was an easier way to perform such a command I would prefer to know.


Solution

  • A) Nested try/catch will work. This simplified code proves it (and will help you to test in any Windows):

    Write-Host "0"
    Try
    {
        Write-Host "1"
        throw
    }
    Catch
    {
        Try
        {
            Write-Host "2"
            throw
        }
        Catch
        {
            Write-Host "3"
        }
        Finally
        {
            Write-Host "4"
            Exit
        }
    }
    Finally
    {
        Write-Host "5"
    }
    

    B) But it is not a good practice. Your error handling code (in catch/finally) code should be robust, easy to read and not do complex things. Just spit the error messages out to help debugging and maybe close some resources.

    However, this is subject to debate... check this SO question.