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

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

results of get-member cmdlet
Figure 1.2

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

information about the restore method
Figure 1.3

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)

restore the database
Figure 1.4

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

Exception calling
Figure 1.5

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)

$server.restore(
Figure 1.6

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

Launch SQL Server Management Studio and log on to Analysis Services
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]

the database
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

Latest Articles