SSAS Cube Backup strategies and Step by Step approach to setup and configure backup

There are different ways to take Cube database backups.

  • SQL Agent Job
    • XMLA script for backup
  • ROBOT job
    • ASCMD command
  • SSIS Package
    • SSIS package
  • AMO (Analysis Management Objects)
    • PowerShell Scripts

I feel AMO (Analysis Management Objects) does our job much easier. Different ways of taking cube backup is explained below. This is going to be a multi server script. I’ve executed this Power Shell scripts from my local PC which connects to remote server and place the backup files (*.abf) on its corresponding backup folder remotely (Default location For Eg:- F:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Backup\<Servername>).

Pre-requisites:-

  • Load SQL modules if it’s not loaded automatically. Please refer my previous post Load SMO and AMO
  • Create a folder with a same name as of servername in a default backup location
  1. For example – Folder named AQBIPTO1 created under a default backup directory
  2. F:\Program Files\Microsoft SQL erver\MSSQL.1\OLAP\Backup\AQBIPT01\ . Its a combination of <Default backup directory location> +<Servername>

You can download complete code here SSAS_Backup

This is continuation of my previous post.

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/05/15/ssas-find-cube-database-name-sizestatus-and-total-size-of-ssas-server/

Please do a complete testing on any of your test server.

Logical Flow

Logical Flow

First Method:  Cube Database Iteration

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

CubeList.txt contains the following the cube databases

ABC
DEF
GHI

PS C:\SSAS> .\SSAS1.PS1 <ServerName>

PS C:\SSAS> .\SSAS1.PS1 AQBIPT01

Copy and Paste the below code into SSAS1.PS1.

 ********************************************************

Param($ServerName=”localhost”)
$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 “c:\SSAS\CubeList.txt”
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()

Second Method: Passing Cube Server and Database as a paramters

Call it through SSMS/SQL Job/PowerShell Console from any machine where server name and database names are passed as its parameters.

PS C:\SSAS> .\SSAS2.PS1 <ServerName> ‘<DatabaseName>’

PS C:\SSAS> .\SSAS2.PS1 AQBITP01 ‘PROD_OLAP’

 Copy and Paste code into SSAS2.PS1.

**************

# 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 “yyyyMMddTHHmm”
$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()

********************************

Run the T-SQL on SSMS or SQL Job:

master..xp_cmdshell ‘PowerShell.exe c:\SSAS_Backup.PS1 AQBITP01 ”PROD_OLAP”’

SQL Job

STEP1:  master..xp_cmdshell ‘PowerShell.exe c:\SSAS_Backup.PS1 AQBITP01 ”PROD_OLAP”’

STEP 2: Delete backup files – Copy and Paste the below code

***********************

Function filedelete
{
Param($ServerName=”localhost”)
$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(-20)}
#write-host $file
foreach($f in $file)
{
$filename=$f.directoryname+’\’+$f.name
write-output ‘File can be deleted’ $filename
remove-item $filename -Force
}
}

Filedelete HQBIPP01

********************************************

Output:-
—————————————————————-
Server : AQBITP01
Database: PROD_OLAP
DB State: Processed
DB Size : 2MB
—————————————————————-
F:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Backup\PROD_OLAP_201305210819.
abf
Successfully backed up PROD_OLAP to F:\Program Files\Microsoft SQL Server\MSSQL.1
\OLAP\Backup\PROD_OLAP_201305210819.abf
NULL

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SSAS and tagged . Bookmark the permalink.

8 Responses to SSAS Cube Backup strategies and Step by Step approach to setup and configure backup

  1. posarao.kodi says:

    Hi Prashanth,

    Please find the below SSAS backup script with log file, here if there is no cube for taking backup the log step getting faile how to avoid it . if there is no SSAS databases my log file has to create some info to avoid the error.

    param
    (
    [string]$ServerInstance = “(local)”,
    [string]$BackupDestination,
    [int]$RententionDays = 5,
    [string]$LogDir,
    [switch]$verbose,
    [switch]$debug
    )

    function main()
    {
    if ($verbose) {$VerbosePreference = “Continue”}
    if ($debug) {$DebugPreference = “Continue”}
    fullbackup $serverInstance $backupDestination $retentionDays $logDir
    }

    function fullbackup($serverInstance, $backupDestination, $retentionDays, $logDir)
    {
    trap [Exception]
    {
    write-error $(“TRAPPED: ” + $_.Exception.Message);
    continue;
    }

    # Force a minimum of two days of retention
    # TIP: using PS “less than” operator
    if ($RetentionDays -lt 5 )
    {
    $RetentionDays = 5
    }

    # Load Microsoft Analysis Services assembly, output error messages to null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null

    # Declare SSAS objects with strongly typed variables
    [Microsoft.AnalysisServices.Server]$SSASserver = New-Object ([Microsoft.AnalysisServices.Server])
    [Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo])

    # Connect to Analysis Server with specified instance
    $SSASserver.Connect($ServerInstance)

    # Set Backup destination to Analysis Server default if not supplied
    # TIP: using PowerShell “equal” operator
    if ($backupDestination -eq “”)
    {
    Write-Debug “Setting the Destination parameter to the BackupDir parameter”
    $BackupDestination = $SSASserver.ServerProperties.Item(“BackupDir”).Value
    }

    # Test for existence of Backup Destination path
    # TIP: using PowerShell ! operator is equivalent to “-not” operator, see below
    if (!(test-path $backupDestination))
    {
    Write-Host Destination path `”$backupDestination`” does not exists. Exiting script.
    exit 1
    }
    else
    {
    Write-Host Backup files will be written to `”$backupDestination`”
    }

    # Set Log directory to Analysis Server default if not applied
    if ($logDir -eq “”)
    {
    Write-Debug “Setting the Log directory parameter to the LogDir parameter”
    $logDir = $SSASserver.ServerProperties.Item(“LogDir”).Value
    }

    # Test for existence of Log directory path
    if (!(test-path $logDir))
    {
    Write-Host Log directory `”$logDir`” does not exists. Exiting script.
    exit 1
    }
    else
    {
    Write-host Logs will be written to $logDir
    }

    # Test if Log directory and Backup destination paths end on “\” and add if missing
    # TIP: using PowerShell “+=” operator to do a quick string append operation
    if (-not $logDir.EndsWith(“\”))
    {
    $logDir += “\”
    }

    if (-not $backupDestination.EndsWith(“\”))
    {
    $backupDestination += “\”
    }

    # Create Log file name using Server instance
    [string]$logFile = $logDir + “SSASBackup_” + $serverInstance.Replace(“\”,”_”) + “.log”
    Write-Debug “Log file name is $logFile”

    Write-Debug “Creating database object and set options…”
    $dbs = $SSASserver.Databases
    $serverBackup.AllowOverwrite = 1
    $serverBackup.ApplyCompression = 1
    $serverBackup.BackupRemotePartitions = 1

    # Create backup timestamp
    # TIP: using PowerShell Get-Date to format a datetime string
    [string]$backupTS = Get-Date -Format “yyyy-MM-dd”
    # Add message to backup Log file
    # TIP: using PowerShell to output strings to a file
    # Write-Debug “Backing up files on $serverInstance at $backupTS”
    # “Backing up files on $ServerInstance at $backupTS” | Out-File -filepath $LogFile -encoding oem -append
    “Check the file” | Out-File -filepath $logFile -encoding oem -append
    Clear-Content $logFile
    # Back up the SSAS databases
    # TIP: using PowerShell foreach loop to enumerate a parent-child object
    foreach ($db in $dbs)
    {
    $serverBackup.file = $backupDestination + $db.name + “_” + $backupTS + “.abf”

    # TIP: using mixed string literals and variable in a Write-Host command
    Write-Host Backing up $db.Name to $serverBackup.File
    $db.Backup($serverBackup)

    if ($?) {$serverBackup.File | Out-File -filepath $logFile -encoding oem -append}
    else {“Failed to back up ” + $db.Name + ” to ” + $serverBackup.File | Out-File -filepath $logFile -encoding oem -append}
    }

    # Disconnect from Analysis Server
    $SSASserver.Disconnect()

    # Clear out the old files and files backed up to the Log file
    # Write-Host Clearing out old files from $BackupDestination
    [int]$retentionHours = $retentionDays * 24 * – 4
    # “Deleting old backup files” | Out-File -filepath $logFile -encoding oem -append

    # TIP: using PowerShell get-childitem (get child items for matching location) and pipe to
    # where-object (selecting certain ones based on a condition)
    # get-childitem ($backupDestination + “*.abf”) | where-object {$_.LastWriteTime -le [System.DateTime]::Now.AddHours($RetentionHours)} | Out-File -filepath $logFile -encoding oem -append
    get-childitem ($backupDestination + “*.abf”) | where-object {$_.LastWriteTime -le [System.DateTime]::Now.AddHours($RetentionHours)} | remove-item
    }

    main

  2. posarao.kodi says:

    SET CLIENT=%1%
    SET SCHED=%2%
    SET SQLSERVERINSTANCE=%3%
    SET FORCEBACKUP=%4%
    IF “%FORCEBACKUP%” EQU “” (SET FORCEBACKUP=0)
    SET SQLSERVER=%SQLSERVERINSTANCE%
    IF “%SQLSERVERINSTANCE%” NEQ “%CLIENT%” (SET SQLSERVER=%CLIENT%\%SQLSERVERINSTANCE%)
    IF “%SQLSERVERINSTANCE%” NEQ “%CLIENT%” (SET SQLSERVER2=%CLIENT%_%SQLSERVERINSTANCE%)
    SET CLASS=%CLIENT%_SQL
    ECHO %CLIENT%
    ECHO %SCHED%
    ECHO %SQLSERVERINSTANCE%
    ECHO %SQLSERVER%
    ECHO %FORCEBACKUP%

    powershell -command “New-Item -Force D:\dbslist, D:\backup_%SQLSERVERINSTANCE% -ItemType directory”

    powershell -command “”

    !!!( SEE HERE I WOULD LIKE TO USE IF CONDTION
    IF EXIST FILENAME D:\backup_%SQLSERVERINSTANCE%\*.abf then only the
    below script has to run )

    “C:\Program Files\Veritas\NetBackup\bin\bpbackup.exe” -c %CLASS% -s %SCHED% -w -f “D:\dbslist\SSASBackup_%SQLSERVER2%.log”

    • Hello,

      I feel, Its better to handle this exception in PowerShell script rather its being handled in Batch file.

      If my understanding is correct,You are trying to schedule a job in some machine, lets say(XYZ) and checking for an existence of abf file on a remote server.

      IF EXIST \\ServerName\E$\Completepath\abc.abf
      (ECHO It exists
      “C:\Program Files\Veritas\NetBackup\bin\bpbackup.exe” -c %CLASS% -s %SCHED% -w -f “D:\dbslist\SSASBackup_%SQLSERVER2%.log”
      ) ELSE (ECHO It doesn’t exist )

      When you try to run it for different version then the default location will be changed. You need to think of handling many condition.

      I suggest you to handle this condition in the PowerShell script, it makes your life easier.

  3. posarao.kodi says:

    please reply for the above batch file here i would like to use if condition but im not getting how to use

  4. posarao.kodi says:

    thanks for your king info finally i fixed it like below it running fine .

    IF NOT EXIST D:\Backup_%SQLSERVERINSTANCE%\*.abf GOTO NOFILES
    ( “C:\Program Files\Veritas\NetBackup\bin\bpbackup.exe” -c %CLASS% -s %SCHED% -w -f “D:\dbslist\SSASBackup_%SQLSERVER2%.log” )

  5. Pingback: SSAS – Powershell Backup cubes | Sauget Charles-Henri – Blog Décisionnel Microsoft

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s