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") try { $bk.SqlBackup($srv) } catch [Exception] { echo $_.Exception.GetType().FullName, $_.Exception.Message } }
The error it gave me was:
Microsoft.SqlServer.Management.Smo.FailedOperationException Backup failed for Server 'POWERSHELL_TEST'.
I changed the line:
echo $_.Exception.GetType().FullName, $_.Exception.Message
to:
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 : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).1210 19-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&Evt ID=Backup+Server&LinkId=20476 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 ueries) 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”!