About three months ago I wrote a handy little powershell script utilizing a variety of functions and cmdlets to download a copy of our production data, restore it to a staging environment, cleanse PCI from the data and configure mirroring. At some point this script stopped working. Unfortunately the error messages regarding why it was not working were as follows:
Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. " Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'DBServer.domain.com'. " At D:\scripts\StagingDBMigrate\DBActions.ps1:60 char:20 + $res.SqlRestore <<<< ($srv) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException
Unfortunately, that doesn’t tell us a whole lot.This is the highest level exception and doesn’t give us much information. Only that the command failed. Here is a handy little piece of code that will help you find the innermost exception that caused your error and spit it out to the host
Trap { $error = $_.Exception while ( $error.InnerException ) { $error = $error.InnerException write-output $error.Message }; continue }
This piece of code will loop through the exception chain until it gets to what should be the root cause of the failure, printing the trace along the way. This turned our previously arcane error message into a cleanly understandable error that looks something like this
Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. " System.Data.SqlClient.SqlError: RESTORE cannot operate on database 'Database' because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.
EDIT:Providing a more complete example:
Function foo { #Define Trap at top. Any exceptions within this function will automatically call this block Trap { Write-Host 'Caught an exception!' -ForegroundColor Red $error = $_.Exception write-Host $error.Message -ForegroundColor Red continue } $exception = [System.IO.IOException] "Dude, there's no file there!"; Write-Host 'Before exception' throw $exception Write-Host 'After Exception' } #Call our Exception handling function! foo
So where do you put the `Trap` statement in relation to the `KillDatabase` statement (or whatever statement you're trying to get the error details for)?
ReplyDeleteAhh I can see how it might no be so obvious where that goes. Essentially it can be put in the top level of the script or function you want to trap the error for. I'll throw up an edit to the blog post to demonstrate this.
Delete