[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

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
rsnellman
Asked:
rsnellman
  • 6
  • 4
  • 2
  • +2
4 Solutions
 
Paul JacksonSoftware EngineerCommented:
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
 
devlab2012Commented:
Take a full database of the backup and then restore the database. During restore, choose the new file location.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
rsnellmanIT ManagerAuthor Commented:
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
 
Lee SavidgeCommented:
Detach is the best way to do it yes. I thought you wanted to move it to the D drive?
0
 
Paul JacksonSoftware EngineerCommented:
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
 
Paul JacksonSoftware EngineerCommented:
Yes use detach in both cases it is the safest way,
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rsnellmanIT ManagerAuthor Commented:
So, I don't need to backup the database to the new location first?  Did I mention I am new to SQL?
0
 
Paul JacksonSoftware EngineerCommented:
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
 
rsnellmanIT ManagerAuthor Commented:
A couple of the databases are using web applications (IIS).  One is an online calendar and the other is a SharePoint Foundation 2010.
0
 
rsnellmanIT ManagerAuthor Commented:
Oh, I see.  Thanks for clearing that up jacko72.
0
 
rsnellmanIT ManagerAuthor Commented:
OK, I have the one database moved.  And it worked like a charm.  Now to figure out the Sharepoint foundation database.
0
 
rsnellmanIT ManagerAuthor Commented:
OK, I have all the databases moved as desired.

Thanks to all.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now