Use PowerShell to Work with Analysis Service Database Backups

ScriptingGuy1

Summary: Learn how to use Windows PowerShell to work with Microsoft SQL Server Analysis Service database backups.

Microsoft Scripting Guy, Ed Wilson, here. Our guest blogger today is Muthusamy Anantha Kumar, a.k.a. “The MAK.”

Muthusamy Anantha Kumar is a Microsoft Certified IT Professional database administrator (MCITP: Database Administrator 2008). He has more than 12 years of experience in information technology including database engineering, database administration, system analysis, design, development, and support of Microsoft SQL Server (2008, 2005, 2000, 7.0, 6.5, 6.0, 4.X) for production, development, and testing.
Currently, MAK is senior database engineer responsible for engineering and standardizing Microsoft SQL servers in a big entertainment company. Previously, he was responsible for development, test, disaster recovery, and production servers for various finance, .com, and business-to-business companies.

He plays an active role in forums as an SQL expert and moderator. He teaches database administration at the University of West Florida and SQL Server part-time in New Jersey. He has published online articles in SQL-Server-Performance.com and SQLServercentral.com, and he is a contributing columnist in DatabaseJournal.com.

MAK has also written a Wrox book with Yan Pan (a columnist for DatabaseJournal.com) titled Microsoft SQL Server 2008 Administration with Windows PowerShell.

If you would like to contact The MAK, please feel free to email him at: mak@mssqlengineering.com

Take it away MAK…

Backups are bread and butter for database administrators, and that includes Microsoft SQL Server Analysis Service database backups. In this blog, I am going to examine the methods and process to back up a SQL Server Analysis Service database by using Windows PowerShell.

SQL Server 2008 and SQL Server 2005 allow us to install multiple instances on the same host. SQL Server Analysis Service can be installed as a default instance or a named instance. So first, we should take both into consideration when we save the information to a variable or use it in the Windows PowerShell script parameter, as shown here.

#Named Instance

$SSASInstance=”PowerPC\NYT_ENG”

Or

#Default Instance

$SSASInstance=”PowerPC ”

Second, let us check if the host where Microsoft SQL Server Analysis Service resides can be pinged. To do that, we need to separate the host name from the instance name. I use the Split function to separate the host name from instance name, as shown here.

$SSASInstance=”PowerPC\NYT_ENG”

$HostName=$SSASInstance.Split(“\”)[0]

If ($SSASInstance -eq $NULL -or $SSASInstance -eq “”)

{

            Throw “Instance name can’t be empty”

            EXIT

}

Write-Host “HostName is $Hostname”

The resulting output is shown in the following image.

I use the Throw command, so that when you use Windows PowerShell cmdlets or scripts that use SQL Server Agent as the scheduler, it can return the report as failed. If the instance name is empty or null, it will throw an error. Please note that you cannot use Write-Host or any printing command when you use SQL Server Agent as the scheduler.

The next step is to find the host where the SQL Server Analysis Service is up and running. The easiest way to check if a server is up and running is by pinging the host name. To ping a host, I use the WMI class Win32_PingStatus as follows:

$PingHost = (Get-WmiObject -Class Win32_PingStatus -Filter “Address = ‘$HostName'”).ProtocolAddress

if ( $PingHost -eq “”)

{

      Write-Host “Step1: $Hostname not up or cannot be reached or not pingable. ”

      Throw “Host: $Hostname not pingable”

    Exit 2

}

else

{

      Write-Host “Step1: $Hostname  is Pingable. Trying to check SQL Server Analysis Service status”

}

Next, we want to connect to the SQL Server Analysis Service. The SQL Server Analysis Service can be accessed by using the Analysis Management Objects, also known as AMO. To use AMO, we need to load the assembly because it is not loaded by default. The following code loads the assembly and creates and initializes an instance of the SQL Server Analysis Service.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) > $NULL

$Server = New-Object Microsoft.AnalysisServices.Server

If ($? -eq $False)

{

      Throw “Cannot load assembly”

      EXIT

}

If you want to explore more information related to $Server (such as methods and properties), you can use the Get-Member cmdlet, as shown here.

$Server|Get-Member

The next step is to find if the database that we are trying to back up really exists in the database collection on the server. This is shown in the following code.

$Database=”DataMart_Sales”

$DBExist=$False

 

$DBCollection=$Server.Databases

ForEach($DBname in $DBCollection)

{

    $DB=($server.databases.item($DBName)).Name

    if ($DB -eq $Database)

    {

        $DBExist=$True

    }

}

 

If ($DBExist -eq $False)

{

       Throw “No Such Database name in the collection”

       EXIT

}

Now it is time to find the backup location from the SQL Server Analysis Service system configuration. The following code gets that information from the server properties and stores it in the variable $BackupDir.

$BackupDir=($Server.Serverproperties |where-Object {$_.Name -eq “BackupDir”}).Value

if ($BackupDir -eq “” -or $BackupDir -eq $NULL -or (Test-Path $BackupDir) -eq $False)

{

       Throw “No Such Backup Directory”

       EXIT

}

Now we want to generate a backup file name. I usually add a time stamp to my backup file name. The following code uses the current date, backup location, and database name to generate the file name.

$Mydate=(Date).ToString()

$Mydate=$Mydate.Replace(“:”,” “)

$Mydate=$Mydate.Replace(“/”,” “)

$Mydate=”_” +$Mydate.Replace(” “,”_”)

$BackupFileName=$BackupDir+”\”+$Database+$Mydate+”.abf”

$BackupFilename

If all the previous steps ran successfully without any errors, it means that:

• The host is pingable.
• The AMO-related assembly is loaded.
• You can connect to the SQL Server Analysis Server.
• The backup location is available.
• The database is available.
• A backup file name has been generated.

Now all we have to do is to back up the database. The following code uses the AMO backup method to back up the database called DataMart_Sales.

$DB=$server.databases.item($Database)

$DB.Backup($BackupFilename,$True,$True,$NULL,$True)

The backup will be stored in the following location:

C:\Program Files\Microsoft SQL Server\MSAS10_50.NYT_SQLENG\OLAP\Backup\DataMart_Sales_5_8_2011_1_59_54_AM.abf

Thank you, The MAK, for sharing your knowledge and your time.

That is all there is to backing up an Analysis Service database by using Windows PowerShell. SQL Week will continue tomorrow when I will talk about querying the SQL error log.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

ScriptingGuy1

Follow