Featured Database Articles

How to Restore an SSAS 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 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.

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]

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]

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.