Backup Analysis Services Database Using PowerShell

We can use PowerShell to backup Analysis Services (AS) multidimensional or tabular database. This is great because it offers us more flexibility in automating the AS database backup process. Typically, we might have multiple Analysis Service databases within an Analysis Services instance. As a good Database Administrator responsible for the Analysis Services, we want to backup all those Analysis Services databases.

The PowerShell script below can be used to backup all databases within an Analysis Services instance. To accomplish this, we use Analysis Management Objects (AMO) to get the list of databases available within the Analysis Services instance. Then we use the Backup-ASDatabase cmdlet to backup each database to Analysis Services backup (.abf) file. Alternatively, we could use the AMO backup method to backup the Analysis Services database, but for this script, I choose to use the Backup-ASDatabase cmdlet.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$ServerName = "ServerName"
$BackupPath = "C:\Temp"

$Dt = Get-Date -f yyyyMMddhhmmss

# Make sure that the backup folder exist.
$BackupLocation = "$BackupPath\$ServerName"
If (!(Test-Path $BackupLocation))
{
   New-Item -ItemType Directory -Force -Path $BackupLocation
}

$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect($ServerName)

# Loop thru each database within the SSAS server.
foreach ($Database in $Server.Databases)
{
   $DatabaseName = $Database.Name
   $BackupFile =  $BackupLocation + "\" + $DatabaseName + "_" + $dt + ".abf"

   Write-Host "Starting to backup $DatabaseName ..." -ForegroundColor Green
   Backup-ASDatabase -Server $ServerName -BackupFile $BackupFile -Name $DatabaseName -ApplyCompression -ErrorAction Stop
   Write-Host "$DatabaseName has been backed up successfully." -ForegroundColor Green
}

Reference

• Backup-ASDatabase cmdlet - The site contains information on how to use the Backup-ASDatabase cmdlet. It also provides you with the different parameters that you can use. You can get the information by running the "get-help Backup-ASDatabase -detailed" on your PowerShell window.