Posts Tagged ‘sql server attach database’

SQL Server – Attach Database with MDF file only

30.01.2013 1 comment

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:DatabaseDataSqlAndMe.mdf
SqlAndMe_log  C:DatabaseLogSqlAndMe_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!