• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Move all databases from default C: to dir on D

I have spent alot of time researching the forum and have found many topics on moving datatbases.  I am new to SQL and have a lot to learn.  I need to move the databases from the default install directory on C: to a new data directory on drive D:.  I am using MS SQL 2000 on Windows Server 2003.  I basically need instructions from start to finish.  I am new to SQL but not to the field of computers.  Be gentile and thank you for your help and patience.

Geoff
0
geoffreydlewis
Asked:
geoffreydlewis
  • 3
  • 2
  • 2
2 Solutions
 
AaronAbendCommented:
go to enterprise manager.

Navigate to the server and the database you want to move

Right mouse

Go to options

Note the names and locations of the Data and Log files for the database you want to move

go to All Tasks

Click "Detach Database"

If there are users on the database you will be notified.. otherwise, it will detach the database

Move the files to your other drive.

Attach the database by right mousing on the Databases folder of Enterprise Manager and navigating to the new location on the D drive.

0
 
AaronAbendCommented:
2 corrections/clarifications:

The names and locations of the files are in Properties, not options when you right mouse on the database
When you close the Properties window, you will have to right mouse again to see the All Tasks option

0
 
geoffreydlewisAuthor Commented:
When I right click on the database that I want to move, Detach Database is greyed out (unselectable).  I am logged in as sa, just to let you know.  Thanks.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Scott PletcherSenior DBACommented:
You are risking a lost db using EM to detach/attach dbs.  You should *always* use Query Analyzer to attach or detach a db.
0
 
geoffreydlewisAuthor Commented:
Thanks for all of your info and help.  After using your advice and referring to this article, http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql, I was able to move the database.

Thanks again.

Geoff
0
 
AaronAbendCommented:
Scott,

I thought EM just executed the same commands you'd execute in QA.  Is there a reference for your info (if you have a chance)?

Thanks,

Aaron
0
 
Scott PletcherSenior DBACommented:
No, just experience with all the people who have had problems, and even corrupted databases, using that method.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now