asked on
sql I moved database files, how do I remap the dbs
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\HOD2
LOG ON(NAME='HOD201210_log', FILENAME='C:\OptionDB\HOD2
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.
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\HOD2
LOG ON(NAME='HOD201210_log', FILENAME='C:\OptionDB\HOD2
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.
Microsoft SQL Server 2008Microsoft SQL Server.NET Programming
Last Comment
Steve Wales
Yes you can. Are you seeing any issues?
ASKER
Sorry, my question is, how do I make the databases point to the new files? J drive vs C drive.
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.
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.
ASKER
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.
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;
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.