Detach and attach Analysis Service Database using Windows PowerShell and SQL Server 2008 AMO

April 1, 2009

Part 1 of this series on “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 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 a Analysis Service database from a backup file with no password protection. Part 7 discussed how to restore an analysis service Database from a database backup file with password protection. Part 8 covered how to process a dimension in analysis service databasej and Part 9 illustrated how to process a single cube and also process the entire Analysis service database. This installment examines the various types of detach and attach methods to detach and attach an Analysis service database.

Let's begin by connecting to the Analysis Service server “PowerServer\SQL2008” using Windows PowerShell and AMO as shown below. [Refer Figure 1.0] Here you are loading the Analysis Service Manage object DLL and connecting to the Analysis Service server instance “SQL2008” on the “PowerServer3” host.

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

Now let's list all of the databases available on the Analysis server “PowerServer\SQL2008” as shown below. [Refer Figure 1.1]

$database=$server.databases
$database|select-object name

Result

Name
----
Advent2
Adventure Works 2008 DW
Adventure Works 2008 -3
AAA
Adventure Works Test
Adventure Works 2008 -2
MyCube
xyz
Marketing
Finance1
Finance2
Finance3
Marketing1
MyDb123

Connect to the “Adventure Works 2008 -2” database on the Analysis Service instance “SQL2008” on the “PowerServer3” host as shown below. [Refer Figure 1.2]

$advdb=$database["Adventure Works 2008 -2"]

Note: The data files related to this database are located under the C:\Data\Adventure Works 2008 -2.4.db folder.

Let's list the various detach options available if you are going to detach the database. [Refer Figure 1.3]

$advdb=$database["Adventure Works 2008 -2"]

Result

MemberType          : Method
OverloadDefinitions : {System.Void Detach(), System.Void Detach(String password
                      )}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : System.Void Detach(), System.Void Detach(String password)
Name                : Detach
IsInstance          : True

From the output, you find that when detaching a database you have an option of protecting the data files using a password. Let's detach the database without adding the password first, as shown below. [Refer Figure 1.4]

$advdb.detach()

Now let's move the C:\Data\Adventure Works 2008 -2.4.db folder to E:\Data, as shown below, using cut and paste or copy and paste in Windows Explorer. [Refer Figure 1.5]

Now let's attach the database back to the “SQL 2008” Analysis Service instance using the attach method.

Note: The method attach is available at the server level and not under the database level.

Before doing this, let's list the various options available if we are going to attach the database. [Refer Figure 1.6]

$Server.attach

Result

MemberType          : Method
OverloadDefinitions : {System.Void Attach(String folder), System.Void Attach(St
                      ring folder, ReadWriteMode readWriteMode), System.Void At
                      tach(String folder, ReadWriteMode readWriteMode, String p
                      assword)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : System.Void Attach(String folder), System.Void Attach(Str
                      ing folder, ReadWriteMode readWriteMode), System.Void Att
                      ach(String folder, ReadWriteMode readWriteMode, String pa
                      ssword)
Name                : Attach
IsInstance          : True

From the result we see that a detached database can be attached back to the Analysis Service database using the attach method with or without using password.

Now let's attach the “Adventure Works 2008 -2” database using the attach method without using a password as shown below. [Refer Figure 1.7]

$server.attach("E:\Data\Adventure Works 2008 -2.4.db","ReadWrite")

You see that the database has attached successfully, without an error.

Now let's detach the same database but this time we protect the data with a password as shown below. [Refer Figure 1.8]

$advdb=$database["Adventure Works 2008 -2"]
$advdb.detach("P@ssw0rd123")

Let's attach the “Adventure Works 2008 -2” database using the attach method without password as shown below. [Refer Figure 1.9]

$server.attach("E:\Data\Adventure Works 2008 -2.4.db","ReadWrite")

From the output you see that the attach method failed because we didn’t provide the password.

Now let's attach the “Adventure Works 2008 -2” database using the attach method with the password we used when detaching it, as shown below. [Refer Figure 1.10]

$server.attach("E:\Data\Adventure Works 2008 -2.4.db","ReadWrite","P@ssw0rd123")

If you want to attach the database and make the database available in ReadOnly mode then you could attach it with the ReadOnly mode option as shown below. [Refer Figure 1.11]

Conclusion

Part 10 of this article series has illustrated the various types of detach and attach methods to detach and attach an Analysis Service database.

» See All Articles by Columnist MAK