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

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

list all of the databases available on the Analysis server

Figure 1.1

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″]

Connect to the

Figure 1.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

list the various detach options available

Figure 1.3

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()

detach the database without adding the password

Figure 1.4

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]

move the C:\Data\Adventure Works 2008 -2.4.db folder to E:\Data

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

list the various options available if we are going to attach the database

Figure 1.6

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”)

attach the

Figure 1.7

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”)

detach the same database but this time we protect the data with a password

Figure 1.8

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”)

attach the

Figure 1.9

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″)

attach the

Figure 1.10

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]

attach the database with the ReadOnly mode

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles