Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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


Comments

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

Loading comments...