Backup of OLAP Database using Powershell


In this article I would like to explain another method to take the backup of OLAP databases via PowerShell. It will help out to take the backup of multiple OLAP databases with a specified retention period. In Tsql, XMLA function is applied however it is a bit puzzling to initiate the similar process for more than one database.

###### Configuration ######
# $server_name
  $server_name = "<Server Name>"
# UNC path of share or on-disk location to which backups will be stored.
  $backup_location = "<Full path of Backup location>"
# Array of databases that will be backed-up. If $null then all databases
 # will be backed up.
  $user_requested_databases = $null
# How long backups will be retained
  $retention_period_in_days = 90

###### End Configuration ######
trap [Exception] {
   write-error $("TRAPPED: " + $_.Exception.GetType().FullName)
   write-error $("TRAPPED: " + $_.Exception.Message)
 if ($server) {
 exit 1
if ($server_name -eq $null) {
 $server_name = "localhost"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
  $server = New-Object Microsoft.AnalysisServices.Server
# Set the directory for backups to the server property
 # "BackupDir" if it's not otherwise specified
  if ($backup_location -eq $null) {
 $backup_location = ($server.get_ServerProperties() | Where {$_.Name -eq "BackupDir"}).Value}
  elseif (!(Test-Path -path $backup_location)) {
 throw "Specified path ($backup_location) does not exist."
# Generate an array of databases to be backed up
 $available_databases = ($server.get_Databases() | foreach {$_.Name})
 if ($user_requested_databases -eq $null) {
 $databases = $available_databases}
 else {
 $databases = $user_requested_databases.Split(",")
 # Check that all specified databases actually exist on the server.
 foreach ($database in $databases) {
 if ($available_databases -notcontains $database) {
 throw "$database does not exist on specified server."
foreach ($database in ($server.get_Databases() | Where {$databases -contains $_.Name})) {
 $directory_path = $backup_location + "\" + $database.Name
 if (!(Test-Path -Path $directory_path)) {
 New-Item $directory_path -type directory | out-null
 [string] $timestamp = date
   $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')
# Cleanup Old Backups
 Get-ChildItem $directory_path | where {
    $_.LastWriteTime -le (Get-Date).AddDays(-$retention_period_in_days)
 } | remove-item


Amit Kumar

Amit Kumar is a Lead DBA and working with SQL Server Technologies since 2007. Where he focuses on core SQL Server Administration and Development.

%d bloggers like this: