Recovering a SQL Server database from a deleted log file

One of my customers called me today curious about what to do if you delete the LDF log file for a database.  To be honest, I hadn't experienced that one in the wild so I had to look it up.  Turns out the recovery process is pretty straight forward.  The server edition was SQL Server 2008 SP1.

Warning: this procedure is not the ideal way to resolve this problem (ref), but in my situation I had no other choice.  This customer's environment was a test environment and backups were disabled for this database.  Here's the rundown on what happened.  The log file ran out of space so he shut down SQL Server (commiting all pending transactions) and deleted the LDF file.  Since I was sure that the database file was in a good state all I needed was to create a new log file.  This is what I did, in your case, replace DBNAME with your database name:

ALTER DATABASE DBNAME SET OFFLINE

ALTER DATABASE DBNAME SET EMERGENCY

ALTER DATABASE DBNAME SET SINGLE_USER

DBCC CHECKDB (DBNAME , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

Got this in return, which is expected (highlighted below) in this case.

File activation failure. The physical file name "S:\Log\DBNAME.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Warning: The log for database 'Encompass' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

Then I ran CHECKDB again to be sure I that the database was consistent.

DBCC CHECKDB (DBNAME) WITH PHYSICAL_ONLY

Afterward, all that was necessary is to bring the database online:

ALTER DATABASE DBNAME SET SINGLE_USER