SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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


PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...