Backing up Analysis Service database using Windows PowerShell and SQL Server 2008 AMO

January 21, 2009

Part 1 of this article series “Microsoft Windows PowerShell and SQL Server 2008 AMO” illustrated how to install Windows PowerShell and connect to an Analysis Service Servername. Part 2 illustrated how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service. Part 3 discussed how to use Windows PowerShell and AMO to get database properties of SQL Server Analysis Service and Part 4 illustrated how to create Analysis Service Database using Windows PowerShell and AMO. Part V is going to illustrate how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO.

Backing up the database is the most important task in any part of SQL Server administration. That includes backing up the analysis service database as well.

Let’s connect to Analysis Server using Windows PowerShell using SQL Server AMO and backup the database “Adventure Works DW 2008” as shown below. [Refer Figure 1.1]

[System.Reflection.Assembly]::LoadWithPartialName
  ("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
$db=$server.databases.item("Adventure Works DW 2008")
$db.backup("c:\backup\Advent2008_1.abf")

Once the above PowerShell script is executed, you see that the backup file, Advent2008_1.abf, is created on the PowerServer3 server under C:\Backup folder. [Refer Figure 1.1]

The backup method accepts the following parameters.

System.Void Backup(String file, Boolean allowOverwrite, Boolean backupRemotePartitions, 
  BackupLocation[] locations, Boolean applyCompression, String password)

To add a password to the backup file while backing up, execute the script below.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
$db=$server.databases.item("Adventure Works DW 2008")
$db.backup("c:\backup\Advent2008_2.abf",$True,$True,$NULL,$True,"ABC123")

Once the above PowerShell script is executed, you see that the Advent2008_2.abf backup file is created on the PowerServer3 server, under C:\Backup folder. [Refer Figure 1.2]

Note: ABC123 is the password for the backup file Advent2008_2.abf.

If you want to add a password to the backup file while backing up and don’t want to compress then you could execute the following script.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
$db=$server.databases.item("Adventure Works DW 2008")
$db.backup("c:\backup\Advent2008_3.abf",$True,$True,$NULL,$False,"ABC123")

Once the above PowerShell script is executed, the Advent2008_3.abf backup file is created on the PowerServer3 server, under C:\Backup folder. [Refer Figure 1.3]

Note: PowerServer3 is the Analysis Server with named instance SQL2008. Please replace the server name with Analysis server name in your environment. Please replace the database name with the actual database name that you want to backup on the Analysis server in your environment.

Now let’s create a Windows PowerShell script that will backup any database on any Analysis server and database that we pass as parameter. Create a file C:\AMOScripts\backup-ssasDB.ps1 using notepad and copy and paste the code below to that file and save it. [Refer Figure 1.3]

#Objective: To backup database on the given Analysis Server 
#Created by: MAK
#Create Date: 9/9/2007
param
([string] $Servername, [string] $databasename, [string] $location, [switch] $compress, [string] $password)
[string] $mydate=date
$mydate=$mydate.replace(":"," ")
$mydate=$mydate.replace("/"," ")
$mydate="_" +$mydate.replace(" ","_")
$filename=$location+ $databasename +$mydate+ ".abf"

## Add the AMO namespace
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName) 
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
else
{
$dbstring=$server.Databases |select-object name| select-string -simplematch $databasename
if ($dbstring)
{
if ($compress)
{$compression=$True
}
else
{
$compression=$False
}
$db=$server.databases.item($databasename)
$db.backup($filename,$True,$True,$NULL,$compression,$password)
write-host "Backup File name is " $filename
}
else
{
 Write-host "Database " $databasename " does not exist on the server $Servername"
 break

}
}

Download the above script from here.

Execute the script to take a backup of the Analysis service database “Adventure Works DB 2008” in the Analysis service instance “PowerServer3\SQL2008” on to the C:\Backup folder on the Analysis server “PowerServer3”

./backup-SSASDB.ps1 "PowerServer3\SQL2008" "Adventure Works DW 2008" "C:\Backup\" -compress "Test123"

Output for the script is shown below. [Refer Figure 1.4]

Note: “PowerServer3” is the Analysis Server name, “SQL2008” is the Analysis Service instance name on the Server PowerServer3. “Adventure Works DW 2008” is the database on the Analysis Server. C:\Backup is the folder name where you want the backup to be created. –Compress is the parameter switch that compresses that backup. “Test123” is the password for the backup file.

If you don’t want to compress the backup, just remove the parameter switch “-compress” when executing the script, as shown below.

./backup-SSASDB.ps1 "PowerServer3\SQL2008" "Adventure Works DW 2008" "C:\Backup\" "Test123"

You will see results similar to the output shown below. [Refer Figure 1.5]

Note that the backup files are suffixed with date and time. [Refer Figure 1.6]

If you were trying to execute the script by passing the wrong server name, you would get the results similar to the output shown below. [Refer Figure 1.7]

Conclusion

This article has illustrated how to backup the Analysis Service database using Windows PowerShell and SQL Server 2008 AMO.

» See All Articles by Columnist MAK