[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Move all databases from default C: to dir on D

Posted on 2004-08-31
7
Medium Priority
?
407 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Accepted Solution

by:
AaronAbend earned 750 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 750 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 70

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 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