Attach database without LDF file

I came across a situation when a database was detached and accidentally the log (.LDF) file was deleted. I wanted to recover the database without having log file.

Here is how to do this.

First I am detaching the database for which files are located here

D:\MSSQL\Data\TestDB.mdf

E:\TransactionLogs\TestDB_log.ldf

USE [master]

GO

ALTER DATABASE [TestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'TestDB'

GO

After detaching the database I manually deleted the LDF file

Now first attach the database without files.

USE master;

GO

EXEC sp_detach_db @dbname = 'TestDB';

GO

At this time you will not see the database in SSMS under databases

Once database is attached use sp_attach_single_file_db SP to attach the DB with just MDF file. LDF file will be automatically created at default location specified in server properties.

EXEC sp_attach_single_file_db @dbname = 'TestDB',

    @physname =

N'D:\MSSQL\Data\TestDB.mdf';

Now run following query to confirm both data and log files

USE TestDB

select * from sys.database_files

--D:\MSSQL\Data\TestDB.mdf

--E:\TransactionLogs\TestDB_log.ldf

Find more details about sp_attach_single_file_db here

http://msdn.microsoft.com/en-us/library/ms174385.aspx

There is another way to recover database without LDF files.

USE master;

GO

sp_detach_db TestDB;

GO

CREATE DATABASE TestDB

      ON (FILENAME = 'D:\MSSQL\Data\TestDB.mdf') FOR ATTACH ;

GO

Find more details about CREATE DATABASE FOR ATTACH here

http://msdn.microsoft.com/en-us/library/ms176061.aspx