SQL Server Storage Engine & Tools (SSQA.net)

Recover from a corrupted transaction log file - on test server only

Say one fine morning you had a call stating one of the databases have lost the transaction log file due to a hardwrare issue (for discussion sake). Then the recovery option is to restore from the latest backup, that is a best practice too. In case if you don't have the recent backup and still you have to recover the database by rebuilding the transaction log file, then SP_ATTACH_DB_SINGLE_FILE_DB will come into picture. This may not be successfull as the BOL reference:

Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.

So what is the option now even DBCC REBUILD_LOG() may not get you the database back in good condition, as it is undocumented and no real stats to say it works. I have followed a procedure by understanding about the database suspect mode in SQL Server, please bear in mind this is not a recommended procedure for any of the production database and only use in test environment at your own risk (I don't vouch any sort of responsibility for data loss using this process).

• First of backup the available data file (.MDF) in case you totally mess up the process.
• On the suspect(ed) database detach from that SQL Instance using SP_DETACH_DB statement. You wouldn't be able to do from Enterprise Manager anyway.
• Create another new database with same name  with a different physical file on the same path where you store the data & log files.
• Stop SQL Server services.
• Rename the newly created database datafile to another new name without .MDF extenstion.
• Now Rename the old database (corrupted) data file (.MDF) to this database name.
• Start SQL Serve services.
• Still you will see the database in suspect mode, don't worry (you haven't yet finished).
• Open up Query Analyzer and run SP_CONFIGURE 'allow updates',1,

  If you set allow updates to 1 using the sp_configure system stored procedure, you must use the RECONFIGURE WITH OVERRIDE statement. This setting takes effect immediately (without a server stop and restart).

• Update the SYSDATABASES system table by finding dbid setting to 32768 for this suspect'd database.

• Restart SQL Server services.