Solved

Move all databases from default C: to dir on D

Posted on 2004-08-31
7
400 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 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