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>

• Enable XP_CMDShell on your local machine Enable XP_CMDShell

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

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