This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
COURSE of the MONTH
13 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql I moved database files, how do I remap the dbs
Posted on 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\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.
[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
2-
2
- +1
6 Comments
Active today
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.
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.
Active today
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;
Active 1 day ago
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.md
GO
ALTER DATABASE [your_log_name]
MODIFY FILE (NAME = Your_Log_File_Name, FILENAME = 'j:\directory\path\Log.mdf
GO
ALTER DATABASE [your_db_name]
MODIFY FILE (NAME = Your_Data_File_Name, FILENAME = 'j:\directory\path\Data.md
GO
ALTER DATABASE [your_log_name]
MODIFY FILE (NAME = Your_Log_File_Name, FILENAME = 'j:\directory\path\Log.mdf
GO
Featured Post
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering 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
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month13 days, 7 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.