Sql And Me

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!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe