Theres been a couple of times when ive needed to attach a database but didnt have the log file (Attaching AdventureWorks is a good example for when you need to do this), my work occasion for needing to do this happened when i stumbled on multiple mdf files on production server with no ldf (very mysterious!)

There’s a couple of ways to solve this problem (and possibly some more i’m not aware of)

All tasks below are based on having two drives for Data and Log files, with the MDF we want to attach in the Data drive

E:\SQL_Data\Data (MDF location)

E:\SQL_Data\Log (LDF location)

 

Manually Attach the Database via the GUI:

Open SSMS -> Right Click on “Databases” -> Attach

Attach1

This opens the Attach Databases window:

atach2

Click “Add”

atach32

Select the Mdf file and Click “OK”

This returns you back to the Attach Databases window. Now showing the Data and Log files.

(Note it says Log File Type “Not Found”)

Select the Log file and click “Remove”

attach5

It should now look like below:

Attach6

Click “OK”  this will close the Attach Databases window, and you should now see the database in your SSMS databases

Attach7

Attach via T-SQL sp_attach_single_file_db

Attaches a database that has only one data file to the current server. sp_attach_single_file_db cannot be used with multiple data files.

Quoted from: http://technet.microsoft.com/en-us/library/ms174385.aspx

USE [master]
GO
EXEC sp_attach_single_file_db @dbname='AdventureWorks2012',
@physname=N'E:\SQL_Data\Data\AdventureWorks2012_Data.mdf'
GO

Attach via T-SQL CREATE DATABASES ON

USE [master]
GO
CREATE DATABASE AdventureWorks2012 ON
(FILENAME = N'E:\SQL_Data\Data\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Advertisements