http://www.sqlservercentral.com/blogs/sqlandme/2013/01/30/sql-server-attach-database-with-mdf-file-only/

Printed 2014/07/26 11:35AM

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:

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


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