Solved

Move all databases from default C: to dir on D

Posted on 2004-08-31
7
401 Views
Last Modified: 2006-11-17
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
Comment
Question by:geoffreydlewis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Accepted Solution

by:
AaronAbend earned 250 total points
ID: 11942686
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
 
LVL 10

Expert Comment

by:AaronAbend
ID: 11942709
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
 

Author Comment

by:geoffreydlewis
ID: 11943705
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 11946837
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
 

Author Comment

by:geoffreydlewis
ID: 11947680
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
 
LVL 10

Expert Comment

by:AaronAbend
ID: 11948052
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11952444
No, just experience with all the people who have had problems, and even corrupted databases, using that method.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question