Tag Archives: SSAS

Automate backups of SSAS cubes

How to automate backups of SSAS cubes!

This article provides how to automate backups of SSAS cubes using Windows PowerShell.

1. Create text files and scipts on C:\Scripts\

a. CubeList.txt

EnterpriseDW
TrackingDW
ETLDW

 

b. Backup_SSAS.ps1

$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:\Scripts\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
$BackupDestination=”\\backup\dbbackup\SQL_Server\SSAS\” + $DB.Name
$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()

 

c. Bacup_SSAS_main.ps1

[string]$backupTS = Get-Date -Format “yyyyMMddHHmm”
powershell -executionpolicy bypass -file C:\Scripts\Backup_SSAS.ps1 > C:\Scripts\Backup_SSAS_Logs\Bacup_SSAS_$backupTS.log

 

2. Create backup log directory

: C:\Scripts\Backup_SSAS_Logs\

 

3. Create a job in SQL Server database

Type : Operating system(CmdExec)

Run as : SQL Server Agent Service Account

powershell -executionpolicy bypass -file C:\Scripts\Backup_SSAS_main.ps1

 

4. Backup log files look like this;

———————-
Server : ABIP01
Database: EnterpriseDW
DB State: Processed
DB Size : 32MB
———————-
\\backup\dbbackup\SQL_Server\SSAS\EnterpriseDW\EnterpriseDW_201508042052.abf
Successfully backed up EnterpriseDWP to \\backup\dbbackup\SQL_Server\SSAS\EnterpriseDW\EnterpriseDW_201508042052.abf
———————-
Server : ABIP01
Database: TrackingDW
DB State: Processed
DB Size : 14MB
———————-
\\backup\dbbackup\SQL_Server\SSAS\TrackingDW\TrackingDW_201508042052.abf
Successfully backed up ETL to \\backup\dbbackup\SQL_Server\SSAS\TrackingDW\TrackingDW_201508042052.abf