If you want to attach a database to a SQL Instance you need both the data file (.mdf) and the log file (.ldf). Without both files you cannot attach a database. Let’s try doing that…
To attach a database from Management Studio:
Right click on Database node and choose attach:
This will open the “Attach Databases” dialogue box, where you can select the .mdf file using “Add…” button:
Note, that is says “Not Found” for log file, if you click on “OK”, it will raise an error:
The detailed error message can be seen by click on “Message” link:
Moral of the story, you can’t attach single .mdf using GUI. You need to use T-SQL to achieve this:
– © 2011 – Vishal (http://SqlAndMe.com)
CREATE DATABASE [TestDB]
ON (FILENAME = 'C:\123\TestDB.mdf')
FOR ATTACH_REBUILD_LOG ;
On successful completion, you will get the below message:
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\TestDB_1.ldf” may be incorrect.
New log file 'C:\123\TestDB_log.ldf' was created.
Hope This Helps! Cheers!
Republished from Sql&Me [31 clicks].
Read the original version here [32134 clicks].