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

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 file is created on the PowerServer3 server

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]

backup file  is created on the PowerServer3 server

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]

backup file  is created on the PowerServer3 server

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]

output for the script

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]

script output

Figure 1.5

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

the backup files are suffixed with date and time

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]

passing the wrong server name results in an error

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

Latest Articles