Handling Errors in PowerShell

As DBAs, we're always working on protecting ourselves from problems by disaster recovery procedures like backups, but in our haste to get a script done, we also are prone to forget the same concept within our code to protect the process from errors. PowerShell provides ways to manage those times when there are errors in the execution of our script so we can recover, and still get the job done.

Trap Statement for Handling Errors

PowerShell 1.0 supported the Trap statement for handling errors, and I still frequently use that in my scripts. PowerShell works within the .NET Framework, so errors have multiple levels. It's rare that the top level error message PowerShell returns will help you solve the problem. Let's take, for example. the case where we want to use the SMO CheckTables('FAST') method, which effectively runs DBCC CHECKDB(N'AdventureWorks', REPAIR_FAST). If we run call this method without error handling, we'll get this message.

Exception calling "CheckTables with "1" argument(s): "Check tables failed for Database 'AdventureWorks'."

Now, that isn't very helpful at all. Here's the Trap function I frequently use.

# Handle any errors that occurTrap {  # Handle the error  $err = $_.Exception  write-output $err.Message  while( $err.InnerException ) {          $err = $err.InnerException          write-output $err.Message          };  # End the script.  break  }

When the CheckTables('FAST') method is called, now I get the following error messages back.

An exception occurred while executing a Transact-SQL statement or batch.Repair statement not processed. Database needs to be in single user mode.

The error message in Figure 1 is much more useful and helps me understand exactly what the problem is and how to address it. Note, the use of the keyword "break" after the semi-colon in the trap statement, which causes the script to terminate after the error is encountered and handled. The keyword "continue" will allow non-terminating errors to continue script execution after being handled.

Try-Catch-Finally Method of Handling Errors

PowerShell 2.0 introduced the Try-Catch-Finally method of handling errors that most .NET developers are more accustomed to and which provides much more flexibility in handling problems that may come up. The additional feature you get in this method of handling errors is that you can specify different types of error-handling for different types of errors.  In this example, we'll run the same CheckTables method, but handle an ItemNotFoundException separately, then handle all remaining errors in a manner similar to what I showed in the Trap statement above.

try {# Connect to the specified instance$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst $db = $s.Databases[$dbname]$db.CheckTables('Fast')}catch [System.Management.Automation.ItemNotFoundException] {write-output "$dbname database not found"}catch {   # Handle the error   $err = $_.Exception   write-output $err.Message   while( $err.InnerException ) {           $err = $err.InnerException           write-output $err.Message          }}finally {write-output "script completed"}

As you can see, this can provide additional flexibility to handle a variety of errors, and the fact that the Try-Catch-Finally blocks are nestable gives you a lot of control over your scripts.

Related: Run Your SQL Server DBCCs and Check Your Errorlog with PowerShell