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.

SQL Agent Backup SSAS Job Step

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!

Backup SSAS with PowerShell

7 thoughts on “Backup SSAS with PowerShell

  • Pingback:Restore SSAS with PowerShell - sqltomato

  • Pingback:Simple error handling in PowerShell - sqltomato

  • August 8, 2019 at 3:28 pm
    Permalink

    Hey when I use Backup-ASDatabase cmdlet it says below:

    Backup-ASDatabase : The term ‘Backup-ASDatabase’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
    spelling of the name, or if a path was included, verify that the path is correct and try again.
    At line:25 char:2
    + Backup-ASDatabase -Server $ServerName -BackupFile $BackupFile -Name …
    + ~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Backup-ASDatabase:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    Which module I should install to get this running.

    Thanks in advance.

    Reply
    • August 16, 2019 at 10:17 am
      Permalink

      Have you installed the sqlserver module? If you’re running a current version of PowerShell then you should run install-module sqlserver (in an administrative PowerShell session). Hope that helps – Thanks for reading!

      Reply
  • June 23, 2020 at 9:02 pm
    Permalink

    Hi – same issue as Nisha – module is installed, other cmdlets work, like “Backup-SqlDatabase”, but “Backup-ASDatabase” doesn’t work, cmdlet not found

    tried installing with my user scope and as admin…no dice….weird

    Reply

Leave a Reply

Your email address will not be published.

Pin It on Pinterest