Earily this morning, I was asked about how to attach a database without the lof file (.ldf). This may happen if someone drop the (.ldf) file incorrectly. For SQL Server 2005 or later, this can be eaisly done via both T-SQL or SQL Server Management Studio (SSMS).

T-SQL

USE master;
GO
-- Create a dummy database for testing
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB ON PRIMARY
(
	NAME = 'TestDB',
	FILENAME = 'E:WorkspaceDataTestDB.mdf'
)
LOG ON
(
	NAME = 'TestDB_Log',
	FILENAME = 'E:WorkspaceDataTestDB_log.ldf'
);
GO

-- Create a dummy table and populate some test data
USE TestDB;
GO
SELECT * INTO dbo.dbs FROM master.sys.databases
-- Check the table data
SELECT * FROM dbo.dbs

-- Set the database offline and delete the ldf file manually from the OS
USE master;
ALTER DATABASE TestDB SET OFFLINE;
GO

-- In a real world, we may start from here (only mdf file left)
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
-- This is how it works - use CREATE DATABASE ... FOR ATTACH or ATTACH_REBUILD_LOG
-- For more information, check: http://msdn.microsoft.com/en-us/library/ms176061.aspx
CREATE DATABASE TestDB ON
(FILENAME = N'E:WorkspaceDataTestDB.mdf')
FOR ATTACH
GO
-- OR
--CREATE DATABASE TestDB ON
--(FILENAME = N'E:WorkspaceDataTestDB.mdf')
--FOR ATTACH_REBUILD_LOG
--GO

-- After attached, check the table data again
USE TestDB;
SELECT * FROM dbo.dbs;

-- Drop the dummy database
USE master;
DROP DATABASE TestDB;
GO

SSMS

Update: As a side note, if the database is not clean shundown, and the ldf file is lost, the Database Engine may not be able to re-create the log file in this case. In that case, there is a undocument option may work (ATTACH_FORCE_REBUILD_LOG). However, you may have data lost after the database created.

Hope it helps.

Tagged: Detach & Attach, Rebuild Log