?
Solved

Move all databases from default C: to dir on D

Posted on 2004-08-31
7
Medium Priority
?
404 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 69

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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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