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
반응형
