Attaching an MDF file without LDF file

Question

Does anyone know if there is a way to attach just a SQL Server MDF file (without .LDF) file. The log file for database got deleted and I have tried to attach the MDF, it does not work. I have tried running the script to attach file but did not help:

USE master;
GO
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012', 
    @physname = 
N'C:\ProgramData\Homefront\Database\DB1.mdf';

Please helpp !!!!

Solution 1

Also try these methods...

CREATE DATABASE AdventureWorks2012 
ON (FILENAME = N'C:\ProgramData\Homefront\Database\DB1.mdf')
FOR ATTACH_REBUILD_LOG
GO

OR Try this...

CREATE DATABASE AdventureWorks2012 
ON  ON (FILENAME = N'C:\ProgramData\Homefront\Database\DB1.mdf')
FOR ATTACH
GO

Solution 2

Put the MDF in your preferred location on the file system. In sql server 2012 the default is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA (for a local machine install on a developers box for example) You will likely see other databases there and an .mdf and .ldf file for each db you currently have.(they can be elsewhere if you or someone has put them elsewhere)...so put the AdventureWorks2012_Data.mdf file there. Now, in SSMS, right click on your local instance "databases" in the SSMS left hand navigator. Select "attach". In the screen that pops up select "add" in the "Databases to Attach" section (upper right). It should pop open the default location folder for your databases and you should see your AdventureWorks2012_Data.mdf file there. Selecting it and clicking OK will take you back to the initial pop up screen. In the lower right you will see the MDF file and the LDF file listed. The LDF shows up by default. Just highlight the LDF and remove it from the list. Then click OK. The attach process will then recreate the ldf for you. (empty of course) and your AW2012 db will be available .

Comments

[Nate S.]

is there a way you can do this from Management studios? when I try it says it cant find ldf. Im trying to attach adventureworks208_data.mdf and I don't have log files

[demokritos]

I got ldf file is corrupted. "Msg 5173, Level 16, State 1, Line 1 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. Log file 'D:\Logs\TFS_Configuration_1.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'TFS_Configuration'. CREATE DATABASE is aborted."

[Stijn]

The answer below helped me, I had to move the file to the C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA directory.