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).
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:\Workspace\Data\TestDB.mdf' ) LOG ON ( NAME = 'TestDB_Log', FILENAME = 'E:\Workspace\Data\TestDB_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:\Workspace\Data\TestDB.mdf') FOR ATTACH GO -- OR --CREATE DATABASE TestDB ON --(FILENAME = N'E:\Workspace\Data\TestDB.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
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.