Rebuilding the transaction log

Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick:

ALTER DATABASE recovery_test_2 SET EMERGENCY;   
ALTER DATABASE recovery_test_2 SET SINGLE_USER;  

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) 
WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation:

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

You asked about the difference between:

• DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS) and
• ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

The thing is that you can run both to rebuild the log file, but with CHECKDB you rebuild the log and check database for integrity errors as well.

Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

1. DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.

2. ALTER DATABASE REBUILD LOG ON... is an undocumented procedure and requires a subsequent DBCC CHECKDB to fix any errors.

Yes, those are two different statements, each doing very different things.

Depending on the state of the database when the file was deleted, you may be able to get up and running by attaching the database and rebuilding the log by using:

EXEC sp_attach_single_file_db 'dbname here', 'file path and name here'

See sp_attach_single_file_db (Transact-SQL) in the product documentation.

Also see this blog post by Paul S. Randal:

EMERGENCY-mode repair: the very, very last resort