How to Restore an SSAS database using Windows PowerShell and SQL Server 2008 AMO

February 4, 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 illustrated how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO. This article illustrates how to restore an Analysis Service database from a backup file.

In “Backing up Analysis Service database using Windows PowerShell and SQL Server 2008 AMO,” we took a backup of the “Adventure Works DW 2008” database, saving it to a file named “Advent2008_1.abf” that had no password protection. We also backed up the “Adventure Works DW 2008” database to a file “Advent2008_2.abf” that did have password protection. Additionally, we took a backup of the “Adventure Works DW 2008” database, to a file “Advent2008_3.abf” that was password protected but was not compressed.

Now let’s try to restore the database “Adventure Works DW 2008” from the file “Advent2008_3.abf”.

Connect to Analysis Server using Windows PowerShell using SQL Server AMO, as shown below. [Refer Figure 1.1]

[System.Reflection.Assembly]::LoadWithPartialName
	("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")

Note: PowerServer3 is the hostname and SQL2008 is the Analysis Service instance name.

From part V of this article, we know that the backup method is defined under object database. However, the restore method is defined under object Server. Let’s query all of the options available under server object. This can be achieved by using the get-member cmdlet as shown below. Execute the following cmdlet. [Refer Figure 1.2]

$server | get-member -Type methods

Result

Attach
BeginTransaction
CancelCommand
CancelConnection
CancelSession
Clone
CommitTransaction
ConcatenateCaptureLog
Connect
CopyTo
CreateObjRef
Disconnect
Dispose
Drop
EndXmlaRequest
Equals
Execute
ExecuteCaptureLog
GetConnectionState
GetDependents
GetHashCode
GetLastSchemaUpdate
GetLifetimeService
GetReferences
GetType
GetUpdateOverwrites
InitializeLifetimeService
NotifyTableChange
Reconnect
Refresh
Restore
RollbackTransaction
SendXmlaRequest
StartXmlaRequest
Submit
ToString
Update
UpdateObjects
Validate

Now let’s see all the options available under the method restore. Execute the following cmdlet as shown below. The command displays the information about the restore method. [Refer Figure 1.3]

$server.restore

From the above cmdlet, we see the following options available for restoring Analysis Service database. Each option has different uses.

System.Void Restore(String file), 

System.Void Restore(String file, String databaseName), 

System.Void Restore(String file, String databaseName, Boolean allowOverwrite), 

System.Void Restore(String file, String databaseName, Boolean allowOverwrite, RestoreLocation[] locations), 

System.Void Restore(String file, String databaseName, Boolean allowOverwrite, 
	RestoreLocation[] locations, RestoreSecurity security),

System.Void Restore(String file, String databaseName, Boolean allowOverwrite, 
 	RestoreLocation[] locations, RestoreSecurity security, String password), 

System.Void Restore(String file, String databaseName, Boolean allowOverwrite, RestoreLocation[] locations, 
	RestoreSecurity security, String password, String dbStorageLocation), 

System.Void Restore(RestoreInfo restoreInfo)

Now let’s restore the database “c:\backup\Advent2008_1.abf” using one of the above listed restore methods, as shown below. [Refer Figure 1.4]

$server.restore("c:\backup\Advent2008_1.abf","Adventure Works 2008 DW",$true)

Note: We used the following method to restore the database.

System.Void Restore(String file, String databaseName, Boolean allowOverwrite)

If you do not use the $true value option in the restore method, you will get the following error complaining that the database by the name “Adventure Works DW 2008” already exists. [Refer Figure 1.5]

Exception calling "Restore" with "2" argument(s): "Backup and restore errors: The 'Adventure Works 2008 DW' object cann
ot be restored because the object already exists. Set the AllowOverwrite setting to True to overwrite the object.
"
At line:1 char:16
+ $server.restore <<<< ("c:\backup\Advent2008_1.abf","Adventure Works 2008 DW")
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Next, let’s restore the database backup file Advent2008_1.abf to a database “Adventure Works test”. [Refer Figure 1.6]

We can use the same restore method to restore the Advent2008_1.abf database as a different database.

$server.restore("c:\backup\Advent2008_1.abf","Adventure Works Test",$true)

Note: Here we used the same method to restore the database. However, we changed the database name from “Adventure Works 2008 DW” to “Adventure Works Test”.

System.Void Restore(String file, String databaseName, Boolean allowOverwrite)

Launch SQL Server Management Studio and log on to Analysis Services, as shown below. [Refer Figure 1.7]

Note: PowerServer3 is the hostname and SQL2008 is the Analysis Service instance name.

After refreshing the database folder in SQL Server Management Studio, you see that the database “Adventure Works Test” is available. [Refer Figure 1.8]

From Figure 1.8, you can see all of the cubes defined in the database “Adventure Works DW 2008” are now available in “Adventure Works Test”.

Conclusion

Part 6 of this series illustrated how to restore an Analysis Service database from a backup file. The next installment will illustrate how to restore an Analysis Service database file with password protection and more.

» See All Articles by Columnist MAK