I am trying to write a piece of code to delete and recreate an MS SQL database in PowerShell using some Invoke-Sqlcmd commands (caused by an error on our systems). I would like to create some unforeseen circumstances in this case if another program on the target machine accesses the database when the drop command is issued. I thought that a good way to do this is to embed one Try / Catch / Finally command inside another, for example:
$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) Do the nesting teams Try / Catch / Finally actually work? and C) Is this type of command sequence a good practice? I do not have a test machine to try this, and if there was an easier way to execute such a command, I would rather know.