SSAS - Discuss various backup methods & Automating cube database(s) backup with PowerShell

**  **
**
   This article awarded Technet GURU gold medal. You are welcome to update the article as needed
**

Introduction

One of the most important responsibilities of a database administrator is to make sure that all(Relational and OLAP) databases are backed up across environments. SSAS backup has a different set of retention and DR requirements to OLTP DB's due to their nature.  SSAS databases can still be mission critical so we need to consider backup options carefully. This post discusses various options and methodology which can be used to initiate SSAS backup.

There are different ways to take Cube database backup. The different methods are given below(PS:Reference section for First four methods)

• **SSMS **
  • SSAS backups can be performed manually through SQL Server Management Studio, SSMS Basic Method
• SQL Agent Job
  • XMLA script for backup, XMLA and ASCMD
• using ASCMD
  • ASCMD command
• **SSIS **
  • SSIS package, SSIS
• AMO (Analysis Management Objects
  • PowerShell Scripts

 This article talks about AMO (Analysis Management Objects) and it’s advantages in making life easier. Analysis Management Objects (AMO) is the complete library of programmatically accessed objects that enables an application to manage a running instance of Microsoft SQL Server Analysis Services

PowerShell script is used for backup cube databases using AMO classes.  The script will backup  all/specific databases of an instance to default backup location/local/remote or n/w share and manage the backup files as per the retention period data.

The Power Shell script executed from the local machine where it connects to remote server and places the files (*.abf) to it's respective backup folder (Default location For eg:- F:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Backup\.

 Pre-requisites

• Setting execution policy Execution Policy PowerShell
• Load modules if it’s not loaded automatically
• Full rights on file share or local or remote location

Logical Flow

   

  

Method 1 : Input text file consists of Cube DB's, Backup to local drive

This method traverse through a file for listed cube database and initiate backup to a default backup folder.

The pre-requisites are

1. Input file
2. Powershell script saved as SSAS1.ps1 
3. Drive Space, though compression is enabled in the script make sure that you've enough space on the target drive

The database cube names are listed in c:\SSAS\CubeList.txt file.  PowerShell script(SSAS1.PS1) traverse through each cube database for backup.

CubeList.txt must contains the cube databases

ABC
CDEF
GHI

Copy and Paste the code into text file and save as SSAS1.PS1

Param($ServerName="localhost",[String]$inputfile )
$loadInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
## Add the AMO namespace
[Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo])
[Microsoft.AnalysisServices.Server]$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
 If ($server.name -eq $null)
            {
             Write-Output ("Server '{0}' not found" -f $ServerName)
             break
            }
$DBList = Get-Content  $inputfile
Foreach($DBName in $DBList)
{
            $DB = $server.Databases.FindByName($DBName)
            if ($DB -eq $null)
            {
             Write-Output ("Database '{0}' not found" -f $DBName)
            }
else
{
Write-Output("----------------------------------------------------------------")
Write-Output("Server  : {0}" -f $Server.Name)
Write-Output("Database: {0}" -f $DB.Name)
Write-Output("DB State: {0}" -f $DB.State)
Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0"))
Write-Output("----------------------------------------------------------------")
$BackupDestination=$server.ServerProperties.Item("BackupDir").value
$serverBackup.AllowOverwrite = 1
$serverBackup.ApplyCompression = 1
$serverBackup.BackupRemotePartitions = 1
if (-not $backupDestination.EndsWith("\"))
            {
                        $backupDestination += "\"
            }
[string]$backupTS = Get-Date -Format "yyyyMMddHHmm"
$serverBackup.file = $backupDestination + $db.name + "_" + $backupTS + ".abf"
$serverBackup.file
$db.Backup($serverBackup)
if ($?) {"Successfully backed up " + $db.Name + " to " + $serverBackup.File }
else {"Failed to back up " + $db.Name + " to " + $serverBackup.File }
 }
 }
 $server.Disconnect()

Syntax:-

PS C:\SSAS> .\SSAS1.PS1  -servername <ServerName>  -inputfile <InputFileName>

Sample call example:-
**
**

PS:\>.\SSAS-Backup.PS1 -ServerName HQBIPP01 -inputfile c:\ssas\HQBIPP01DB.txt

**Output:-
**

Method 2: Pass cube server ,database  and retention period as it’s parameters

This process backup each listed cube database and delete the corresponding backup entry depending on the retention parameter also the process creates a backup folder(ServerName) in the default backup folder

The pre-requisites are

1. Powershell script saved as Backup-SSAS.ps1
2. Drive Space, though compression is enabled in the script make sure that you've enough space on the target drive
3. Retention Period by default its 10 days

Copy and Paste the code into Backup-SSAS.ps1
**
**

 
###### Configuration ######
 
    #  FQDN of Analysis Services server. If no server name is specified thendefaults to localhost.
      #backup_location is the default backup folder
    #  Only specified database will be backed-up.
    #  How long backups will be retained, by default its 15


##### End Configuration ######

param([String]$ServerName="localhost", [String]$DBName="", [int]$RetentionPeriod=15 )
 
## Add the AMO namespace
$loadInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
[Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo]) 
[Microsoft.AnalysisServices.Server]$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
 
$DB = $server.Databases.FindByName($DBName)
if ($DB -eq $null) {
 Write-Output ("Database '{0}' not found" -f $DBName)
 break
}
 
Write-Output("----------------------------------------------------------------")
Write-Output("Server  : {0}" -f $Server.Name)
Write-Output("Database: {0}" -f $DB.Name)
Write-Output("DB State: {0}" -f $DB.State)
Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0"))
Write-Output("----------------------------------------------------------------")
 
$BackupDestination=$server.ServerProperties.Item("BackupDir").value
$serverBackup.AllowOverwrite = 1 
$serverBackup.ApplyCompression = 1 
$serverBackup.BackupRemotePartitions = 1
if (-not $backupDestination.EndsWith("\")) 
    {
        $backupDestination += "\"
    } 
 
[string]$backupTS = Get-Date -Format "yyyyMMddHHmm" 
$serverBackup.file = $backupDestination +$servername+'\'+ $db.name + "_" + $backupTS + ".abf" 
$serverBackup.file
$db.Backup($serverBackup) 
if ($?) {"Successfully backed up " + $db.Name + "  to " + $serverBackup.File }
else {"Failed to back up  " + $db.Name + " to " + $serverBackup.File } 
$server.Disconnect() 
$drive=$serverBackup.file.substring(0,1)
$path='$serverBackup.file | Measure-Object -Character |select  characters'
$path=$serverBackup.file.substring(2,$path.characters)
write-host $path
$file=get-ChildItem \\$servername\$Drive$\$path -recurse -Filter $DBName*.abf | Select-object LastWriteTime,directoryname,name | where-object {$_.LastWriteTime -lt [System.DateTime]::Now.AddDays(-$RetentionPeriod)}
foreach($f in $file) 
{
$filename=$f.directoryname+'\'+$f.name 
write-host 'File deleted' $filename
remove-item $filename -Force
}

**Syntax:-

**

PS:\>.\Backup-SSAS.ps1 -ServerName <ServerName> -DBName <DBName> -RetentionPeriod <InDays>

Sample call:-

PS:\>.\Backup-SSAS.ps1 -ServerName hqbipp01 -DBName GL -RetentionPeriod 1

Output:-
**

**

Method 3:  Backup all cube database to Network Share

###### Configuration ######
 
    # FQDN of  Analysis Services server. If no server name is  specified then
    # defaults to  localhost. example: $server_name = "ssas1.microsoft.com"
    $server_name = $null
 
    # UNC path of  share or  on-disk location to which backups will be stored.
    # Do not including trailing slash. If null  then defaults to SSAS BackupDir 
    # example: $backup_location = "\\storage.microsoft.com\ssas-backup"
    $backup_location = $null
 
    # 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 = 30
 
    ###### End  Configuration ######
 
    trap [Exception] {
        write-error $("TRAPPED: " + $_.Exception.GetType().FullName) 
        write-error $("TRAPPED: " + $_.Exception.Message)
        if ($server) {
            $server.disconnect()
        }
        exit 1
    }
 
    if ($server_name -eq $null) {
        $server_name = "localhost"
    }
 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
    $server = New-Object Microsoft.AnalysisServices.Server
    $server.connect($server_name)
 
    # 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(' ','-')
        $database.Backup("$directory_path\$database-$timestamp.abf")
 
        # Cleanup Old Backups
        Get-ChildItem $directory_path | where  { 
            $_.LastWriteTime -le (Get-Date).AddDays(-$retention_period_in_days)
        } | remove-item
    }
 
    $server.disconnect()

Execute PowerShell script from SSMS 

MASTER..XP_CMDSHELL 'PowerShell.exe c:\SSAS_Backup.PS1 HQBIPP01 ''GL'''

  SQL Job

SQL Jobs has two steps. 

• SSAS Cube Backup - to initiate backup
• Filedelete - for Rentention

 

 STEP1:  master..xp_cmdshell 'PowerShell.exe c:\SSAS_Backup.PS1 HQBIPP01 ''GL'''

 

 STEP 2:  Manage Retention

Function filedelete
{
Param($ServerName="localhost",[int]$RentionInDays)
$loadInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
[Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo])
[Microsoft.AnalysisServices.Server]$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
$BackupDestination=$server.ServerProperties.Item("BackupDir").value
if (-not $backupDestination.EndsWith("\"))
            {
                        $backupDestination += "\"
            }
[string]$backupTS = Get-Date -Format "yyyyMMddTHHmm"
$serverBackup.file = $backupDestination +$servername+'\'+ $db.name + "_" + $backupTS + ".abf"
#write-host $serverBackup.file
$server.Disconnect()
$drive=$serverBackup.file.substring(0,1)
#write-host $drive
$Ppath=$backupDestination +$servername
#write-host $Ppath
$path=$Ppath | Measure-Object -Character |select characters
$len=$path.characters
#write-host $len
$path=$serverBackup.file.substring(2,$len-1)
#write-host $path
$file=get-ChildItem \\$servername\$Drive$\$path -Filter *.abf | Select-object LastWriteTime,directoryname,name | where-object {$_.LastWriteTime -lt [System.DateTime]::Now.Addhours($RentionInDays)}
#write-host $file
foreach($f in $file)
{
$filename=$f.directoryname+'\'+$f.name
write-output 'File can be deleted'  $filename
remove-item $filename -Force
}
}
#In this case 20 days is the Retention period
 Filedelete HQBIPP01 20

** **

Conclusion

• Centralized management of cube database backup for various server
• One place to refer various methods to create backup of analysis services cube databases
• Retention period customization for all files and also for specific cube database backup file
• Backup Compression 
• Backup to local or remote or fileshare
• Effective backup strategy is always good for a Disaster Recovery

Reference

Basic SSAS backup using SSMS

• http://sqlmag.com/sql-server-2008/backup-basics-sql-server-analysis-services

SQLAgent, ASCMD and XMLA Script

• http://blogs.msdn.com/b/as_emea/archive/2012/02/14/ssas-2008-automating-analysis-services-backup.aspx

SSIS

• http://dba.stackexchange.com/questions/43493/how-can-i-dynamically-back-up-all-ssas-databases-on-a-given-instance

PowerShell AMO

• https://zakird.com/code/2011/06/20/backing-up-analysis-services-in-powershell/ 

Backup Basics

• http://technet.microsoft.com/en-us/library/bb522603%28v=sql.110%29.aspx

Load snapins

• http://msdn.microsoft.com/en-us/library/cc281962.aspx