Restore SSAS with PowerShell

A Backup of SSAS database needs a Restore SSAS to work as a completed unit and, as they say, “a backup is only as good as your last restore”.

Backup SSAS database

In my post Backup SSAS with PowerShell we created the code below to backup a SSAS database (cube) with a timestamp. We also deleted older versions of the backup. In the script we retained three days of backups.

$d = get-date -f yyyyMMddhhmmss
Backup-ASDatabase -server Server\Instance BackupNameandLocation_$d.abf "SSAS Database Name" -ApplyCompression
Get-ChildItem -Path "BackupLocation" -Filter "*.abf" -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt (Get-Date).AddDays(-3)} | Remove-Item -Force 

Restore SSAS database

The next step is to build in an additional step to automatically restore the database to another copy of the database. We’ll use a standard named database to restore to and call it “SSAS Database Restore”.

Now using the PowerShell Restore-ASDatabase cmdlet we’ll create the following PowerShell command below.

Restore-ASDatabase -Server Server/Instance -RestoreFile BackupNameandLocation_$d.abf -Name "SSAS Database Restore"

Since we’re going to be restoring over the “SSAS Database Restore” restored database, we’ll need to apply the -AllowOverwrite flag.

Backup and Restore SSAS

For the example below we have a SSAS database called sqltomato. This database is backed up to the “C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Backup” directory (one of the default locations in SQL Server 2016), with the embedded timestamp. Then all backups with the .abf (Analysis Services Backup File) file older than three days are deleted. Finally, we’ve added the Restore SSAS database section. This restores the database with overwrite to a database in Analysis Services called “SSAS Database Restore”.

$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
Restore-ASDatabase -Server SQLTOMATO -RestoreFile sqltomato_$d.abf -Name "SSAS Database Restore" -AllowOverwrite

Next we’ll put all this in an SQL Server Agent job so we can run the backup, and restore confirmation, on a schedule.

That’s our backup and restore process running! Now we need to cover some error handling in the PowerShell to ensure the backups and restore compete successfully.

Thanks for reading!