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 !!!!

Answer 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

Answer 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 .