Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

HELP!!!  Need to move database from one drive to another on the same server

Posted on 2011-03-15
14
Medium Priority
?
291 Views
Last Modified: 2012-05-11
Hi, I am new to sql server arena, but I have been tasked to setup SQL Server 2005 x64 SP4 on a Server 2008 R2 box, which I have.  I thought I was installing the SQL Server  on the D: drive, but apparently not.  So, when I made the databases for some reason they are in the default location on the C: drive (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).  I would rather have them on the D: drive which has several terabytes of space.  (D:\SQLServer\Data).

Also, there are a couple of databases on the D: drive that I would like to move into this D:\SQLServer\Data folder.

What is the process of accomplishing these tasks?

Thanks in advance.


0
Comment
Question by:rsnellman
[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
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35138387
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 1800 total points
ID: 35138389
Right click the database select detach, copy the mdf and ldf files to where they need to be.
Right click databases and select attach, browse to the new location
0
 
LVL 13

Assisted Solution

by:devlab2012
devlab2012 earned 200 total points
ID: 35138421
Take a full database of the backup and then restore the database. During restore, choose the new file location.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rsnellman
ID: 35138424
Do I need to do this detach process if the database is on the same drive?  (Ex:  I have a database in a folder that I would like to move to a different folder on the same drive.)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35138432
Detach is the best way to do it yes. I thought you wanted to move it to the D drive?
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 1800 total points
ID: 35138437
To update the default locations after you have done the move:

Right click the SQL server instance and select properties.
Click Database Settings
Change Database default locations.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35138443
Yes use detach in both cases it is the safest way,
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138444
You can alter database also:


ALTER DATABASE database_name SET OFFLINE;

ALTER DATABASE <your db name here> MODIFY FILE 
(name=<logical file name>, filename='<path to file>)

--copy your files

ALTER DATABASE database_name SET ONLINE;

Open in new window

0
 

Author Comment

by:rsnellman
ID: 35138449
So, I don't need to backup the database to the new location first?  Did I mention I am new to SQL?
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 1800 total points
ID: 35138469
It is wise to do a backup first. It is always wise to do a backup before doing anything, but it doesn't need to be to the new location. You will copy the data and log files to the new location.
0
 

Author Comment

by:rsnellman
ID: 35138489
A couple of the databases are using web applications (IIS).  One is an online calendar and the other is a SharePoint Foundation 2010.
0
 

Author Comment

by:rsnellman
ID: 35138498
Oh, I see.  Thanks for clearing that up jacko72.
0
 

Author Comment

by:rsnellman
ID: 35138700
OK, I have the one database moved.  And it worked like a charm.  Now to figure out the Sharepoint foundation database.
0
 

Author Comment

by:rsnellman
ID: 35141743
OK, I have all the databases moved as desired.

Thanks to all.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

This article explains how to install and use the NTBackup utility that comes with Windows Server.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

618 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