Print full exception in PowerShell try/catch block using “format-list”

I recently started PowerShell programming and I like it! It feels much like bash or another scripting language system engineers tend to love (like Ruby/Python/Perl). While writing some backup automation (Windows 2012) I ran into the following:

          Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
          Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll'

          $server = "POWERSHELL_TEST"
          $path = "C:\Backup\DB\"

          # Connect to the specified instance
          $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') ($server)

          foreach ($Database in $srv.Databases | where {$_.IsSystemObject -eq $False})
              $dbname = $Database.Name
              $tStamp = Get-Date -format yyyy_MM_dd_HHmmss
              $bckfile = $path.TrimEnd('\') + "\" + $dbname + "_backup_" + $tStamp + ".bak"
              echo "backing up $dbname to $bckfile"
              $bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
              $bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
              $bk.BackupSetName = $dbname + "_backup_" + $tStamp
              $bk.Database = $dbname
              $bk.CompressionOption = 1
              $bk.MediaDescription = "Disk"
              $bk.Devices.AddDevice($bckfile, "File")
              catch [Exception]
                echo $_.Exception.GetType().FullName, $_.Exception.Message

The error it gave me was:

Backup failed for Server 'POWERSHELL_TEST'.

I changed the line:

echo $_.Exception.GetType().FullName, $_.Exception.Message


echo $_.Exception|format-list -force

And this dramatically improved the output:

SmoExceptionType : FailedOperationException
Operation        : Backup
FailedObject     : [POWERSHELL_TEST]
Message          : Backup failed for Server 'POWERSHELL_TEST'.
HelpLink         :
Data             : {HelpLink.ProdName, HelpLink.BaseHelpUrl, HelpLink.LinkId, HelpLink.ProdVer...}
InnerException   : Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing
                   a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: BACKUP DATABASE WITH COMPRE
                   SSION is not supported on Web Edition (64-bit).
                   BACKUP DATABASE is terminating abnormally.
                      at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Obj
                   ect execObject, DataSet fillDataSet, Boolean catchException)
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Executi
                   onTypes executionType)
                      --- End of inner exception stack trace ---
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Executi
                   onTypes executionType)
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlComman
                   ds, ExecutionTypes executionType)
                      at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
                      at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection q
                      at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
TargetSite       : Void SqlBackup(Microsoft.SqlServer.Management.Smo.Server)
StackTrace       :    at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
                      at CallSite.Target(Closure , CallSite , Object , Object )
Source           : Microsoft.SqlServer.SmoExtended
HResult          : -2146233088

Actually the reason was right there:

BACKUP DATABASE WITH COMPRESSION is not supported on Web Edition (64-bit).

So next time you run into an exception and need to print it, use “format-list”!


Leave a Reply

Your email address will not be published. Required fields are marked *