SQL Server - Moving Database Components

Question

Post reply

SQL Server - Moving Database Components

HelpMeInOhio

Hall of Fame

Points: 3945
More actions
June 2, 2004 at 9:02 am

#163673

Hello Team!

I know this is probably an easy question for most of you, but for someone just new in the job iit is something I would like more direction on before I start moving on. So any reply you can provide is appreciated.

I am running out of space on some drives with Data, logs, and Index's. We are in a windows 2000 and SQL Server 2000 environment. I have other drives that have more space. Questions:

1. How do I move a DB to another drive? reloacate and change config .. is there any timing or access restrictions I need to consider? Any problems I should expect? Any specific commands or gui interfaces you would recommend I use?

2. What is the cost when the DB and log are not on the same drive?

3. How do I estimate how long the process will run (Relocate and reconfig)?

4. Do I need to lock users out while this is going on?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32864 More actions · Answer 1

1. Actually very simple. Get all users and processes in a database out of it, back it up and restore via GUI restore and change to default location to the new drive. Its as simple as that. This will work for all application databases just not the system ones. If you change to default location of the system dbs you have to go in and update the configuration so when SQL Server starts it will know where they are. BUT those dbs are small, master, msdb, model, TEMPDB.

2. Sometimes there is a cost to pay. If you have a low end db with not much activity I would say it doesn't matter... others may suggest otherwise though.

3. Length of time, depends on the db size... a backup and then restore...

4. Yes, you need to keep users out during this process.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

There's an even easier way to move database files. But you should still have them backed up first.

Detach the database(s). (You can use either Enterprise Manger - easiest or TSQL commands).

Move the database files (.mdf and .ldf) to their new location.

Attach them. Using Enterprise Manger, the GUI will ask you where the files are. Using TSQL tell it where the files are.

For TSQL commands, check out the Books OnLine (BOL) which comes with SQL Server. It can be found at Start>Programs>Microsoft SQL Server>Books OnLine. Use the index tab and enter DETACHING DATABASE FROM SERVER.

-SQLBill