Restore Analysis Services database using Windows PowerShell and SQL Server 2008 AMO

Part 1 of “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 an Analysis Service Database using Windows PowerShell and AMO. Part 5 illustrated how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO. Part 6 of this article series illustrated how to restore an Analysis Service database from a backup file with no password protection. Part 7 of this article series is going to illustrate how to restore an Analysis Service Database from a database backup file with password protection. It will also illustrate how to change the default Database storage location to a new Database Storage location when restoring.

In part 5 of this article, we took a backup of “Adventure Works DW 2008” database to a file named “Advent2008_1.abf” with no password protection. We also took a backup of “Adventure Works DW 2008” database to a file named “Advent2008_2.abf” with password protection. Thirdly, took a backup of “Adventure Works DW 2008” database to a file called “Advent2008_3.abf” with password protection and no compression. In Part 6 we restored the Analysis service database backup file “Advent2008_1.abf” with no password protection.

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

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