Attach a single MDF file – Database

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:

image

This will open the "Attach Databases" dialogue box, where you can select the .mdf file using "Add…" button:

image

Note, that is says "Not Found" for log file, if you click on "OK", it will raise an error:

image

The detailed error message can be seen by click on “Message” link:

image

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

About these ads
  1. February 10, 2013 at 9:25 pm

    THanks for sharing ..

  1. January 30, 2013 at 5:05 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 275 other followers

%d bloggers like this: