PowerShell - Nesting Try/Catch/Finally Commands

Question

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.

Doane

You are definitely can nest them. I would wonder in your case if you can avoid it by check the state of the table before hand (I dont know how to do this at all. ). That alter command ... Why not just set it offline in the first place? – Kinetics

Hi Matt, I did look into this and it is possible by querying the database for Open Connections. The method I have proposed definitely doesn't seem like the most graceful way of doing what I want so I may investigate this course of implementing some defensive coding into the proceedings. – Doane

Just to follow up on this, I've found the following information; if you set a variable to be Microsoft.SqlServer.Management.Smo.Server, you can use the "GetActiveDBConnectionsCount("foobar")" method to output the current number of connections to a particular database. – Doane

@Doane Setting the db in an offline state can be fairly slow and resource intensive. I think a better bet would be to use set single_user, disconnecting everyone else and rolling back any uncomitted transaction (= just as safe, but much faster). Still with immediate rollback though – Mythology

Answer 1

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.

Ignazio

The reason I wish to do this is that my intention is to feed this script down our chain of Support Analysts, so ease of use and automation was my priority. Because of their lack of experience in diagnosing SQL issues I wanted the code to try and handle this. I'll look into Matt's method above for checking Open Connections in the Database. – Doane

I noticed that setting Break inside the inner catch still executes the final clause instead of terminating the script. (I only tested the case where the inner trycatcher lacks finalization, though.) Is that required that each trycatcher has a finalization statement if any of them does? (Also, I so do agree that the nested trycatching is a poor design of code so my question is rather academic, not pragmatic.) – Yogini

Answer 2

Depending on how you are handling errors . Normally when I run automations I run my scripts to silently continue on error.

In your case try adding

$ErrorActionPreference = 'Stop'

Before you first try block If you want to swap it back if you have longer scrips you can revert it back to

$ErrorActionPreference = 'SilentlyContinue’

After you last catch block

Try that

Freiburg