Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 21, 2009

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

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date