Simple error handling in PowerShell

In this post we will add some simple error handling in PowerShell to complete the Backup and Restore SSAS database script.

In previous posts we looked into Backing up SSAS database with PowerShell and Restoring the SSAS database with PowerShell.

Stop on error

Firstly we’ll add the line below;

$erroractionpreference = "Stop"

In our error handling in PowerShell we’ll use this line to make our PowerShell script stop on error, the default behaviour is to continue. This works for PowerShell cmdlets.

Try Catch, simple error handling in PowerShell

The final piece of the puzzle is to use a simple Try Catch to catch any error and throw an exception if the Restore of our database does not succeed, as below;

Try
{
  Restore-ASDatabase -Server SQLTOMATO -RestoreFile sqltomato_$d.abf -Name "SSAS Database Restore" -AllowOverwrite
}
Catch
{
  throw "Exception in Backup-ASDatabase"
}

We can also test the above error handling in PowerShell by firstly ensuring the Analysis Server database “SSAS Database Restore” exists and then running the job without the -AllowOverwrite option. This should throw and error. Replacing the -AllowOverwrite option allows the job to run successfully as expected.

SQL Agent job and notifications

Next we’ll create an SQL Agent job to run the whole command set and send an Alert if it fails as below. It is generally good practice to write to the windows application event log any errors as the event log can be a first responders initial point of investigation.

The complete code is below. Replace the SQLTOMATO server, “sqltomato” database and, if appropriate, the backup location.

$erroractionpreference = "Stop"
$d = get-date -f yyyyMMddhhmmss
Backup-ASDatabase -server SQLTOMATO -BackupFile "C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Backup\sqltomato_$d.abf" -Name "sqltomato" -ApplyCompression
Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Backup" -Filter "*.abf" -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt (Get-Date).AddDays(-3)} | Remove-Item -Force
Try
{
Restore-ASDatabase -Server SQLTOMATO -RestoreFile sqltomato_$d.abf -Name "SSAS Database Restore" -AllowOverwrite
}
Catch
{
  throw "Exception in Backup-ASDatabase"
}

Thanks for reading!