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

jackjohnson44
jackjohnson44 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
TempDBA

Commented:
Yes you can. Are you seeing any issues?
jackjohnson44

Author

Commented:
Sorry, my question is, how do I make the databases point to the new files?  J drive vs C drive.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Attend Free Webinar
jackjohnson44

Author

Commented:
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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented: 
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.
Senior Database Administrator
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial