Printed 2017/08/21 09:58PM

SQL Server – Attach Database with MDF file only

By Vishal.Gajjar, 2013/01/30

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:


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



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:


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:


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]



SELECT      name, physical_name

FROM        sys.database_files



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!


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

Filed under: Backup & Recovery, Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012
Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.