SQL Server – Attach Database with MDF file only

Earlier on my blog I posted about how you can attach a database using T-SQL when no log file is available. You can catch that here. In this post we will see how it can be attached using GUI.

To attach a database with no .ldf file, follow below steps:

1. Open Attach Database dialog box by selecting "Attach" from context menu of database node in Object Explorer:

image

2. Click "Add" button to locate the .mdf file, and click "OK":

image

image

3. Once you locate the .mdf file for database, it will automatically fill details for log file name and path, also a message will be displayed if .ldf is not available where it should be:

image

4. Since we do not have .ldf file available, select the row for log file and click on "Remove", and then click on "OK" to attach the database:

image

5. This will force SQL Server to rebuild log file for the database. Once the database is attached you can verify that log file has been rebuilt using following T-SQL:

USE [SqlAndMe]

GO

 

SELECT      name, physical_name

FROM        sys.database_files

GO

Results:

name          physical_name
———–   ———————–
SqlAndMe      C:\Database\Data\SqlAndMe.mdf
SqlAndMe_log  C:\Database\Log\SqlAndMe_log.LDF

(2 row(s) affected)

 

You can also do this using T-SQL, read Attach a single MDF file – Database for details.

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 26, 2013 at 1:58 pm

    Hello Vishal,

    This method may get fail to attach the mdf file when database was not cleanly shutdown. At such situation try below steps to attach the mdf file to SQL server:

    1. Create a same size database
    2. Now shutdown the SQL server
    3. Change the old mdf file with new one
    4. Start the SQL server, your database may go in suspect mode
    5. Now change your database status from suspect mode to emergency mode by ALTER Database command
    6. Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair.

    See my article from here: http://sqldbfriend.blogspot.in/2013/02/need-to-attach-mdf-file-without.html

  1. No trackbacks yet.

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 288 other followers

%d bloggers like this: