Solved

sql I moved database files, how do I remap the dbs

Posted on 2012-12-29
6
714 Views
Last Modified: 2012-12-29
I had originally created my databases by copying the files and running the sql below.  I had to move the files to a different drive.

They were in: C:\OptionDB
They are now in : J:\OptionDB

Here is the command that I used to create the database.

CREATE DATABASE HOD201210
  ON(NAME='HOD201210', FILENAME='C:\OptionDB\HOD201210.mdf')
  LOG ON(NAME='HOD201210_log', FILENAME='C:\OptionDB\HOD201210_log.ldf')
  FOR ATTACH WITH ENABLE_BROKER;
Go



I am also not sure if I can open sql server management studio since I moved the files before remapping or detaching.
0
Comment
Question by:jackjohnson44
[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 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38729679
Yes you can. Are you seeing any issues?
0
 

Author Comment

by:jackjohnson44
ID: 38729716
Sorry, my question is, how do I make the databases point to the new files?  J drive vs C drive.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38729925
You can open SSMS at any time. Did you stop SQL Server while moving the files? I'll hope so, else you should not have been able to move all files, as they were in use.

In SSMS, you should see the DB now as not active (suspect, damaged or something like that).
Switch it to Offline, Detach, Attach selecting the new MDF location, and correct all file locations shown as "not found".
You can do the same with SQL, of course, but since this is a one-timer (hopefully), I would prefer the "manual" method using the GUI.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:jackjohnson44
ID: 38729942
Thanks, I did turn it off.  I was actually hoping for a script, I have a large number of those databases and going into the properties will take too long.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38729977
alter database HOD201210 set offline;
sp_detach_db N'HOD201210'
CREATE DATABASE HOD201210 
  ON(NAME='HOD201210', FILENAME='J:\OptionDB\HOD201210.mdf') 
  LOG ON(NAME='HOD201210_log', FILENAME='J:\OptionDB\HOD201210_log.ldf') 
  FOR ATTACH WITH ENABLE_BROKER;

Open in new window

I'm not sure that a simple drop of the DB would help here (guess not), hence used offline/detach/attach as with the GUI.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38729980
As long as you haven't moved the system databases you should be able to do this:

ALTER DATABASE [your_db_name]
MODIFY FILE (NAME = Your_Data_File_Name, FILENAME = 'j:\directory\path\Data.mdf');
GO
ALTER DATABASE [your_log_name]
MODIFY FILE (NAME = Your_Log_File_Name, FILENAME = 'j:\directory\path\Log.mdf');
GO
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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