So far in this series we’ve only looked at SQL Server database backups, but PowerShell can also be used to backup and restore SQL Server Analysis Server objects.

Prior to the release of the latest version of SQLPS with SQL Server 2012, SSAS backups were achieved via the Backup method of a Analysis Server Database object:

Import-Module SQLPS -DisableNameChecking

$SSASInstance = "SSASServer1\Instance1"
$DBName = "db1"

$BackupFile = $DBName+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".abf"

$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.connect($SSASSInstance)

$DB = $SSASServer.databases[$DBName]
$DB.backup($BackupFile)

Not that we don’t have to build an backup object, or any devices. So this is all a lot simpler than a Data Platform backup. However there is less flexibility. Some other options are available as the Backup method is ‘overloaded’ (it has a number of different implementations). All the possible options are listed here.

Parameter Type Action
File String Filename to backup to
allowOverwrite Boolena Whether the backup can overwrite an existing file, default is now
backupRemotePartitions Boolean Whether remote partitions should be backuped up, default is no
RemoteLocations An array of Analysis Server Backup locations Where remote partitions should be backed up to
ApplyCompression Boolean Whether to compress the backup or not, default is off
Password String Password to encrypt backup files

Just to make life slightly more awkward you need to specify every parameter before the last one you want to use. So if you want to set Compression on, then you need to specify File, AllowOverwrite, backupRemotePartitions and RemoteLocations as well, even if just specifying empty strings like so:

$DB.backup($BackupFile,$False,$False,NULL,$True)

With SQLPS as provided with SQL Server 2012, there is another way. The CmdLet Backup-ASDatabase was introduced, which works in much the same way as Backup-SQLDatabase:

Backup-ASDatabase -BackupFile $backupfile -Server $SSASInstance -Name $DB -ApplyCompression

This is slightly easier to work with, and can be automated for all the dbs in an instance just like we did for the Data Platform examples:

Import-Module SQLPS -DisableNameChecking

$SSASInstance = "SSASServer1\Instance1"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.connect($SSASSInstance)

foreach($DB in $SSASServer.databases){
    $BackupFile = $$DB.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".abf"
    Backup-ASDatabase -BackupFile $backupfile -Server $SSASInstance -DataBase $DB -ApplyCompression
}

And as a bonus, this format is backward compatible with SQL Server 2008 and SQL Server 2008R2, you only need the SQL Server 2012 SQLPS module installed, which comes with the SQL Server client tools (free to download and use if you grab SQL Server 2012 Express SP1 or better). So it could make your life easier if you grab this even if you upgrade nothing else to 2012.

Note that all the usual Analysis Services restrictions on what is actually backup up apply when backing up via PowerShell as they would via any other method.

One advantage of backing up SSAS via PowerShell is that it’s very easy to ensure that the underlying datastores are also backed up at the same time.

Tomorrow we’ll look at restoring SSAS Databases.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.