How to Attach and Detach a SQL Server Analysis Services Database

Sharing buttons:

well let's talk a couple of other things

we've just got two little final topics

to close out here in Chapter four what I

want to show you is a little about

database management we want to talk to

you about how to move databases so

that's what this video is about how to

move a database in analysis services you

might be moving a database from server

to server from instance to instance from

disk to disk from folder to folder and

the second thing that we want to talk

about is the disaster recovery dealing

with backup and restore in analysis

services and we'll do that one in the

next video so this video it's actually

about a new feature here in analysis

services 2008 the ability to attach and

detach those of you coming from a sequel

server management background you

probably do this you know fairly

consistently you attach a database or

you detach a database in analysis

services we have the same need so you

know we can do it now it's actually not

that difficult yeah it scratch that it's

actually kind of difficult because of

the weird user interface designs that we

have to deal with

so let's actually take a look so I'm

assuming that you went through and

completed the exercise 2 with me so you

have the sample you want to follow along

that's great

I didn't write an exercise for this one

I figured that you can kind of follow

along and do what I'm doing if you

wanted to follow along so what the first

thing that I need to do I need to go

ahead and close my cube over here you

can see we have an MDX query open up

here I need to go ahead and close that

because that creates an open connection

rule is you cannot detach when you have

an open connection ok so I close my that

my query there so here's what I want to

do I'm gonna come up with a little

scenario here and sometimes it's easier

for me to write it then you have to

depend on understanding my accent so

yeah and I know that to some of you

you're probably going what accent but I

mean you know we have customers and 7080

countries out there so I'm sure lots of

people think I have an accent

so let's think you kinda have to play

along we did we just purchased a new set

of hard drives and now and have added

them as drive J we now want to move our

analysis services database to this

location the rule is one minimal

downtime that's the the rule that we

have to play so what are the techniques

that we would go through what are the

possibilities well you really have two

choices here

you could either do an attach or you

could do a backup in the wrist store

I'll do the backup and restore in this

video we'll do the backup and restore

I'll do the attach and detach in this

video and I'll do backup and restore in

the next video I think for this type of

stuff I would opt for the detach and

attach okay the process of detaching and

attaching in case you're not really

familiar with the term is you are

basically going to take the database

offline that's the detach now when we

detach it it has no more ties to this

instance of analysis services it is

strictly a folder with analysis services

files in it they're not tied to any

particular instance so that means that

you can take them you can move them

across the network you can move them to

a new drive you can move them to a new

server you can attach them to a

different instance ok so you're not

making a copy of the database you are

simply changing where it is ok so when

you a detached when you do a detach

that's no longer an analysis services

database your users can't connect to it

anymore you can't connect to it anymore

it is physically out of the server right

when you connect that analysis Services

is not there ok

I'll show you that okay so I'm gonna do

this guy here I'm going to detach it and

when I detach it I have the option to

give it a password and the password is

really designed to protect you so that

nobody gets access to your files in the

interim until it's reattached to another

analysis services machine so you can put

yourself a password and I'll put my very

secure password in here you see what I

mean it's gone no amount of refreshing

brings that database back but have I

deleted it at the disk level no it's

still at the disk okay so now what I

would do we want to move it to the J

Drive right gotta remember where it is

located for this one it was in the

default if you'll remember and so I need

to go to the properties of the server

and I need to find out what the data dir

value is so I can just copy that I can

go to run I can paste that in and it

loads it up okay so see I could have

done it in Windows Explorer as well

whichever way you want to do that thing

but so here's where it's tricky and a

lot of folks don't really grasp this

because it is so radically different

from how the sequel server database

engine will store files this is it

this is what you move around okay I just

I want to move it I cut it or copy it

and probably be a safer place I would go

to my J Drive which I don't really have

one let's pretend that this is the J

Drive and I move the folder to the new

location I move it to the new server I

copy it to tape and then put it back on

the other machine and so I have the

database folder in the proper place this

is the Adam J Drive okay so now I want

to attach it so I want to now put it

back on the server we've moved the

database files now we want to put it

back so let's go back over here

I come and I ask it to attach so far so

good right nothing if you've done sequel

server attached attached before this is

nothing out of the ordinary but now it

is okay so you go to attach it you go to

your folder draft do you remember that

browsable folders list it's such a pain

to have to deal with this here so now

what you actually have to do you have to

add that folder into the browsable

folders list they see this is not the

path to your folder and and yeah this is

just a horrible screen I'm with you a

hundred percent agree thing neither of

these folders contains your database

here's the thing that you have to do you

must put the path to the database folder

in your browsing folders list and you

have to be in that path you can't be

like at a subfolder above it it won't go

down and do that you have to be at that

level so here's what you have to do

right it tells you down here at the

bottom you have to go modify the allowed

browsing folders cancel cancel back over

here but I don't see allowed browsing

folders well that's because you need to

check your show advanced then you get

the allowed browsing folders go get the

path to it so go drill into it and then

just copy this is all I ever do right

there a drill into the folder and then I

just copy from Windows Explorer and I go

back over here and you want to add

rather than replace the value so I go to

the very end so I just I get into the

value text box I go to the end and I put

the pipe character you know the one that

I'm talking about right it looks just

like a cursor and it's just a pipe that

is this separator this is basically an

array and I just paste and you can see

it now I have this

is the new part that I've added right

there okay so this becomes now a new

entry because of the pipe character and

that's where I'm going so that's the

path into the database folder and when I

say okay doesn't require a server

restart for this to get propagated from

value to current value no so as soon as

I say okay

that's now going to be in the browsing

folders list I can come back over here

and I can say attach and when I click

right there now you see it being listed

and you see your new location there it

is that's the one we want to choose I

say okay you got to put your password in

say okay and you've done it now I will

tell you I have had some gremlins creep

in with respect to attach and detach

from time to time I've had errors I've

had security issues I've had some errors

with being able to detach the database

again so test this out very thoroughly

on your you know machines your

development your test machines before

you actually try to do something like

this in production yeah this is kind of

still a new feature for analysis

services so don't be shy about putting

it through its paces before you're ready

to go with production level on this one

well let's come back in the next video

and we'll take a look at doing a backup

in a recovery