We help IT Professionals succeed at work.

SQL Database reconnect

MS SQL server on a C drive, database files on D.
OS had issues, had to rebuild the OS on C.
D files were untouched.
Installed OS 2019, and MS SQL server 2017 on C.
Created an instance with the same original name but pointed the data folder location to another folder.
(I didn't want anything inadvertently overwritten).
Original data files at D:\Microsoft SQL Sever\
New location of data at D:\MS SQL Server\

How can I connect the new instance to the original files?
Comment
Watch Question

Database Analyst
Commented:
This could be an easy one if no database corruption occurred when C:\ failed OR....but lets hope the SQL Database files from D:\Microsoft SQL Sever\ are in good shape so this is what I would do:

COPY (don't move so you have the original files untouched on that D:\Microsoft SQL Sever\) all the USER database files from D:\Microsoft SQL Sever\ to D:\MS SQL Server\
Do not copy master,tempdb,msdb,model, and any other System DB's
You may need to also check at file level in D:\MS SQL Server\ to make sure they were not switched to READONLY
 at OS level when C:\ crashed.
Open SQL SSMS and expand it then right click the "Databases" and use the "Attach" function under the menu and navigate to D:\MS SQL Server\ and select one by one the MDF files that have the DB information.
NOTE: they will be attempted to be attached as the info was written in the MDF from original location so please make sure to change each file location/name to match the copy from D:\MS SQL Server\ and NOT the original files from D:\Microsoft SQL Sever\

I would advice to hit the "Script" button at the top instead of hit the OK button on the UI window because you will see EXACTLY what SQL will do to attache/move the files and also you will get any errors in clear text and make sure to save them.

Worst case they will go in recovery or some other state due to corrupted LFD file and we could deal with those at one by one scenario depending on the errors when you try to attache them.

Few useful links below:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15
https://www.mssqltips.com/sqlservertip/1894/attach-sql-server-database-without-log-file/
https://gunnarpeipman.com/attaching-sql-server-database-without-log-file/
HuaMin ChenProblem resolver

Commented:
Hi,
How about that you do Database backup and after you've rebuilt the server OS in C drive, and have also re-setup SQL server, you then restore SQL server into new server OS?
Distinguished Expert 2019

Commented:
What was the original setup?windows OS and SQL server versions?

Author

Commented:
Thanks Icohan; we ended up using some .bak files to restore.
But your solution, I believe, could have worked and your comments the most thorough.
Thank you