Backup SSAS with PowerShell

To Backup SSAS databases, one of the easiest and simplest ways to is to use PowerShell.

Versioning our backups

Firstly we want to timestamp each backup so we can version our backups and make it easy, human readable form, for us to choose which file we need. We’ll do this using PowerShell Variable Substitution. So firstly we declare a variable and set it to the current date and time. (e.g. 20160628130000 would be “28th June 2016 13:00:00”)

$d = get-date -f yyyyMMddhhmmss

Creating our backups

Next we’ll backup SSAS using the PowerShell Backup-ASDatabase cmdlet.

This command backs up the specified database SSAS Database Name from the named server Server\Instance to a .abf (Analysis Services Backup File) file. Notice the $d after the backup name and location. This is where the previously mentioned Variable Substitution happens and the backup file gets a timestamp appended to it. (i.e. MyDatabaase_20160628130000.abf)

Backup-ASDatabase -server Server\Instance BackupNameandLocation_$d.abf "SSAS Database Name" -ApplyCompression

Next we need to Apply Compression with the flag. Generally I am a supporter of compression as it reduces the amount of IO required with usually a negligible cost of processor cycles. Although your mileage may vary its always worth looking at backup compression.

We will also need to delete previous versions of the .abf files created. The following line force removes all files with the .abf extension older than three days.

Get-ChildItem -Path "BackupLocation" -Filter "*.abf" -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt (Get-Date).AddDays(-3)} | Remove-Item -Force

The Plumbing

Finally we need to add some plumbing so the above is scheduled and run by the SQL Server Agent.

Remember that the SQL Agent account or, preferably and better practice, a PowerShell (as above pxyPowerShell) proxy account for this task needs modify access to the backup location above. Creating a credential and proxy account for this backup task is well worth the extra few minutes.

Thanks for reading!