Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 18, 2009

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

By Muthusamy Anantha Kumar aka The MAK

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]

[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. We now know the different options available in the restore method. You can see the value information from the restore method. [Refer Figure 1.2]

$server.restore

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

The above cmdlet basically used the method shown below.

System.Void Restore(RestoreInfo restoreInfo)

All the available restore methods are listed below.

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)

We need one more library for restoring the database since we protected the database backup with a password. So let’s execute the following cmdlets as shown below. [Refer Figure 1.3]

We need one more library for restoring the database since we protected the database backup with a password.
Figure 1.3
$security= New-Object Microsoft.AnalysisServices.RestoreSecurity
$security.value__ = 1

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

$server.restore("c:\backup\Advent2008_2.abf","Adventure Works 2008 DW", $true,$NULL,$Security,"ABC123")

restore the database
Figure 1.4

The above command restored the “Adventure Works 2008 DW” database from the “C:\Backup\Advent2008_2.abf” file. In the above example, we used the following method to restore it.

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

We could restore the same database file with a different database name--say “Adventure Works 2008 -2” using the same method. [Refer Figure 1.5]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Security") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$location= New-Object Microsoft.AnalysisServices.RestoreLocation
$security= New-Object Microsoft.AnalysisServices.RestoreSecurity
$server.connect("PowerServer3\SQL2008")
$security.value__ = 1
$server.restore("c:\backup\Advent2008_2.abf","Adventure Works 2008 -2 ", $true,$NULL,$Security,"ABC123")

restore the same database file with a different database name
Figure 1.5

Launch SQL Server Management Studio to see that the database has been restored with the new name. [Refer Figure 1.6]

Launch SQL Server Management Studio
Figure 1.6

If we want to restore the database backup file “Advent2008_2.abf” and move the database storage location to a different folder rather than the default folder when restoring, then we can use the method shown below.

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

Let’s restore the database backup file “Advent2008_2.abf” as “Adventure Works 2008 -3” using the above method. Here we are going to change the Database location to E:\Data. [Refer Figure 1.7]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Security") >$NULL

$server = New-Object Microsoft.AnalysisServices.Server
$location= New-Object Microsoft.AnalysisServices.RestoreLocation
$security= New-Object Microsoft.AnalysisServices.RestoreSecurity
$server.connect("PowerServer3\SQL2008")
$security.value__ = 1
$server.restore("c:\backup\Advent2008_2.abf","Adventure Works 2008 -3", $true,$NULL,$Security,"ABC123","E:\Data\")

change the Database location
Figure 1.7

After restoring, you see the database “Adventure Works 2008 -3” appear in SQL Server Management Studio. [Refer Figure 1.8.]

Notice that the Data files are restored to a new Database Storage location E:\data\Adventure Works 2008 -3.3.db folder. [Refer Figure 1.8 and 1.9]


Figure 1.8

Notice that the Data files are restored to a new Database Storage location
Figure 1.9

Conclusion

Part 7 of this article series has illustrated how to restore an Analysis Service Database from a database backup file with password protection. It also illustrated how to change the default database storage location to a new database storage location when restoring.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM