SSAS: Automate SSAS Backups With PowerShell

I was looking for a solution to automate SSAS backups using PowerShell. By “automate”, I mean the following:

• Backup one or more SSAS databases in an SSAS instance to a network location
• Automatically cleanup backups that are older than [x] days
• Schedule the backups via SQL Server Agent with minimal overhead
• Backup multiple host instances/databases with one call/script
• Have only a single PowerShell backup script out on a network share that does everything and is referenced from all Agent backup jobs passing in custom parameter values for databases, location, retention days etc

As it turns out, my friend and MVP SQLSwimmer already has something going for second bullet above which I have borrowed and incorporated with minor changes in my script. The rest, I put together. The reason I created this is because I wanted the solution to not involve any additional steps like having to script out and setup or change the backup script every time there is a change in DB list (or even change script for a brand new server) and wanted the script to discover that and act accordingly.

Pre-requisites on SQL Server Host:

• SQL Server Agent
• Assembly Microsoft.AnalysisServices.Server
• Permissions for the Agent Service Account to local or UNC path
  • Read permissions to the PowerShell script
  • Full-Control permissions to the backup folder

The PowerShell Script:

WordPress messes-up PowerShell block comments so I am going to separate the comments at the beginning from the code. You can combine them when you use it.

The Comments

This should go inside PowerShell block comments at the beginning of the script)

#v1.0  - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com] - Initial Version - Feb.21.2019

#######################
.SYNOPSIS
    Creates SSAS backups and optionally removes older backups in the backup folder

.DESCRIPTION
    Given an SSAS instance, backs up one or all SSAS databases to a given local server path or a network path
    Optionally, older backup files for the database can be removed by giving a retention period in days

    If * is specified for DatabaseName parameter, it loops through all database on instance and does these
    Steps:
    1. Rename the existing backup file from say MyHost_Instance.MySSAS_DB.abf to MyHost_Instance.MySSAS_DB.20190221123140.abf
    2. Make a fresh backup say MyHost_Instance.MySSAS_DB.abf
    3. Cleanup backups older than specified BackupRetentionDays days

.PARAMETER
    Server - The servername/hostname without the instance name

.PARAMETER
    Instance - Use "default" (or) empty string if no instancename else the instancename

.PARAMETER
    $DatabaseName - * is the default. The specific database name to backup (or) '*' backup all databases

.PARAMETER
    BackupPath - Local server path on the server (or) network backup location

.PARAMETER
    BackupRetentionDays - Backups older than this many days will be deleted from path resursively

.EXAMPLE

    #This example backups up all SSAS databases on instance
    #
    $splatParams = @{
        Server = 'MYHOSTNAME'
        Instance = 'MYINSTANCENAME'
        DatabaseName = '*'
        BackupPath = "\\UNCPath\With\Access\To\ServiceAccount\"
        BackupRetentionDays = 30
    }

    \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams

.EXAMPLE

    #When scheduled from an Agent job as a OS command, it needs to be on a single line like this:

    PowerShell.exe -ExecutionPolicy bypass "$splatParams = @{Server='MYHOSTNAME'; Instance='MYINSTANCENAME'; DatabaseName='*'; BackupPath='\\UNCPath\With\Access\To\ServiceAccount\'; BackupRetentionDays=30}; \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams;"

.NOTES
    File rename/deletion source:
    https://sqlswimmer.wordpress.com/2015/02/11/automating-ssas-backups/

    Requires: Assembly Microsoft.AnalysisServices.Server

The Code

param( 

    [Parameter(Mandatory=$true)]
    [string] $Server,                    #The servername/hostname without the instance name

    [Parameter(Mandatory=$true)]
    [string] $Instance,                  #Use "default" (or) empty string if no instancename else the instancename

    [Parameter(Mandatory=$false)]
    [string] $DatabaseName = '*',        #The specific database name to backup (or) '*' backup all databases

    [Parameter(Mandatory=$false)]
    [string] $BackupPath = 'C:\Backups', #Local path on the server (or) network backup location

    [Parameter(Mandatory=$false)]
    [int] $BackupRetentionDays = 300     #Backups older than this many days will be deleted from path resursively
)

try
{
    #To avoide the error “(script).ps1 is not digitally signed.  The script will not execute on the system.”
    $stepName = "Changing execution policy for session"
    #--------------------------------------------
    Write-Verbose $stepName
    #Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -Force    

    if ($Instance.ToUpper().Trim().Equals('DEFAULT') -or
         $Instance.ToUpper().Trim().Length -eq 0)
    {
        $connString = $Server
    }
    else
    {
        $connString = "$Server\$Instance"
    }

    $stepName = "Making connection to server/instance [$connString]"
    #--------------------------------------------
    Write-Verbose $stepName  

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
    $serverObj = New-Object  Microsoft.AnalysisServices.Server
    $serverObj.Connect($connString)

    $stepName = "Loop through databases in the instance"
    #--------------------------------------------
    Write-Verbose $stepName  

    foreach ($database in $serverObj.Databases) {

        #If a database name is specified, backup just that one
        #   ...else backup all databases if it is empty or '*'
        #
        if (($DatabaseName.Trim().Length -eq 0)  -or
            ($DatabaseName.Trim() -eq '*')  -or
            ($DatabaseName.ToUpper() -eq $database.Name.ToUpper()))
        {
            $DatabaseNameInLoop = ($connString.Replace('\', '_') + '.' + $database.Name)

            #---------------------------------------------------------------
            #1. Rename the existing backup file by appending datetime to the name
            #---------------------------------------------------------------

            $stepName = "Rename existing backup"
            #--------------------------------------------
            Write-Verbose $stepName  

            $now = Get-Date -f "yyyyMMddHHmmss"
            $backupFilePathAndName = ($BackupPath | Join-Path -ChildPath "$DatabaseNameInLoop.abf")
            $newname = ($BackupPath | Join-Path -ChildPath "$DatabaseNameInLoop.$now.abf")

            if (Test-Path -LiteralPath $backupFilePathAndName -PathType Leaf)
            {
                $stepName = "Renaming $backupFilePathAndName to $newname"
                #--------------------------------------------
                Write-Verbose $stepName  

                Rename-Item $backupFilePathAndName $newname
            }

            #---------------------------------------------------------------
            #2. Make a fresh backup
            #---------------------------------------------------------------

            $stepName = "Starting Backup of OLAP DB: [$DatabaseNameInLoop] to [$backupFilePathAndName]"
            #--------------------------------------------
            Write-Verbose $stepName  

	        $database.Backup($backupFilePathAndName)

            #---------------------------------------------------------------
            #3. Cleanup backups older than x days for current database
            #---------------------------------------------------------------

            $stepName = "Removing files older than [$BackupRetentionDays] days for database $DatabaseNameInLoop"
            #--------------------------------------------
            Write-Verbose $stepName  

            $retentionDate = (Get-Date).AddDays(-1 * $BackupRetentionDays)

            Get-ChildItem $BackupPath -Recurse -Include "$DatabaseNameInLoop.*.abf" |
                        Where {($_.CreationTime -le $retentionDate)} |
                        Remove-Item –Force -ErrorAction Stop

        }
    }

    $serverObj = $null
}
catch
{
    [Exception]$ex = $_.Exception
    Throw "Unable perform SSAS backups! Error in step: `"{0}]`" `n{1}." -f `
                    $stepName, $ex.Message
}
finally
{
    #Return value if any

}

Examples:

The usage examples are in the comments but I will go over some examples

Backup all databases on an instance:

To backup all the databases on an instance, the script is simple enough. Just pass in * for the DatabaseName parameter

    $splatParams = @{
        Server = 'MYHOSTNAME'
        Instance = 'MYINSTANCENAME'
        DatabaseName = '*'
        BackupPath = "\\UNCPath\With\Access\To\ServiceAccount\"
        BackupRetentionDays = 30
    }

    \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams

Backup a database on default instance:

To backup all the databases on an instance, the script is simple enough. Just pass in * for the DatabaseName parameter

    $splatParams = @{
        Server = 'MYHOSTNAME'
        Instance = 'default'
        DatabaseName = 'MyDatabase'
        BackupPath = "\\UNCPath\With\Access\To\ServiceAccount\"
        BackupRetentionDays = 30
    }

    \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams

Backup all databases on default instance of multiple hosts:

To backup all the databases on an instance, the script is simple enough. Just pass in * for the DatabaseName parameter

@('MyHost1','MyHost2','MyHost3','MyHost4','MyHost5') |
ForEach-Object {
    $splatParams = @{
        Server = $_
        Instance = 'default'
        DatabaseName = '*'
        BackupPath = "\\UNCPath\With\Access\To\ServiceAccount\"
        BackupRetentionDays = 30
    }

    \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams
}

Setting up scheduled backups with SQL Agent:

Setting this up with SQL Server Agent as the default type of “PowerShell” for “Command Type” will not work. There is some trickery involved and let us go over it. Resist the urge to define your step as a PowerShell script inside SQL Agent job. It will not work as the shell is very limited in terms of what it can and cannot do. Also running the code from the sample will work in regular PowerShell but it needs to go through a syntactic change when called from SQL Agent.

Prerequisites and setup steps

If you name the script Backup-SSAS.ps1 for example place it somewhere

• The SQL Agent Service Account needs read privileges to that location

If you will write the backups to an UNC path or another path

• The SQL Agent Service Account needs full-control to the folder to be able to create, rename and remove files

The Agent job step has to be of type “Operating System (CmdExec)” as shown in the picture below:

The call itself is how it will look when called from the OS, running in the command prompt for example. The complete command below needs to be on a single line. You can change the parameters as necessary but this is what the “Command” should be for the step:

PowerShell.exe -ExecutionPolicy bypass "$splatParams = @{Server='MYHOSTNAME'; Instance='MYINSTANCENAME'; DatabaseName='*'; BackupPath='\\UNCPath\With\Access\To\ServiceAccount\'; BackupRetentionDays=30}; \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams;"

Please scroll right above to see the full content but it is here again as HTML so that you can see it

PowerShell.exe -ExecutionPolicy bypass “$splatParams = @{Server=’MYHOSTNAME’; Instance=’MYINSTANCENAME’; DatabaseName=’*’; BackupPath=’\\UNCPath\With\Access\To\ServiceAccount\’; BackupRetentionDays=30}; \\UNCPATH\PowerShell\Script\Location\Backup-SSAS.ps1 @splatParams;”

It is pretty self-explanatory. We are calling PowerShell and passing in a long parameter. The long parameter value is the complete command that PowerShell needs to run.If you want to test this, you can take this complete text and run it in the DOS command window to make sure it works before you place it inside a SQL Job Step. The paths of the script as well as the backups could be local paths on the Server. If the “ExecutionPolicy” is not set to Bypass above, you may get errors such as:

The file \\PathToYourScript\Backup-SSAS.ps1 is not digitally signed. You cannot run this script on the current system. For more information about running scripts and setting execution policy see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

I am not going to insult your intelligence by adding images on how to schedule the agent job. You are smart enough to take it from here.

Known Issues:

There is a known issue in that it can cleanup the backups of other databases on the same instance if the database names are overlapping (e.g., MySSASCube and MySSASCube2) and if the retention is different. I need to fix that some time.

Conclusion:

This is a simple enough script with simple pre-requisites. You can use this to backup all of your SSAS servers and databases from a single central Agent Job or schedule multiple jobs. The cleanup of old backups is automatic based on the BackupRetentionDays parameter. Good luck.