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
- For example – Folder named AQBIPTO1 created under a default backup directory
- 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.
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
Prashanth Jayaram 