Recover a database with a DAMAGED and/or LOST log file

In this procedure we’ll manage one of the worst situation a DBA has to manage: corrupted files and data loss. When this heppen usually the common way is restoring but we’ll use sql server features to reduce stop time (avoiding a complete restore) and data loss.

Possible starting problems:
Corrupted logfile
Corrupted logfile during a long transaction
Logfile volume corrupted or lost during transactions

At this point there are different solutions following current database settings:

SCENARIO 1: No transactions running during crash.
Solution:
If no transactions were running at crash point the solution is easy.This because SQL server rebuild automatically lost log file during database startup. So:
1) Detach corrupted database
2) Rename the old corrupted logfile in *.OLD
3) Attach database using:

CREATE DATABASE [MYDATABASE] ON
 ( FILENAME = N'D:Microsoft SQL ServerYourDataPathDataDatabase.mdf' )
 FOR ATTACH_REBUILD_LOG
 GO
 Notes:
 - SQL Server will try to rebuild log file in the ORIGINAL path.

SCENARIO 2: Transactions running during crash
Solution:
ATTACH_REBUILD_LOG in this situation *IS NOT* allowed because SQL Server find open transactions in the database and pending rollback/rollforward operations. So you’ll find the following error trying:

“File activation failure. The physical file name “D:Microsoft SQL ServerYourDataPathDataLogfile.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.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘MYDATABASE’. CREATE DATABASE is aborted. “

So, follow this procedure:
1) DETACH DATABASE MyDatabase
2) Rename datafile and logfile in MDF.OLD and LDF.OLD
3) Create a new database with THE SAME name and identical original datafile and logfile position. I
4) ALTER DATABASE MyDatabase SET OFFLINE
5) Now you can put the original datafile in the original position
6) ALTER DATABASE MyDatabase SET ONLINE. This will fail but now we’ll can rebuild the log file
7) ALTER DATABASE [MyDatabase ] REBUILD LOG ON (NAME=’MyDatabaseLog’,FILENAME=’D:Microsoft SQL ServerYourDataPathDataLogfile.ldf’)
At this point the database will be usable but SQL Server at the end will show this warning:
Warning: The log for database ‘MyDatabase’ 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.
8) Final Step: open the database to users:
ALTER DATABASE [nomedb] SET MULTI_USER

Notes:
– In recovery model FULL make a new FULL BACKUP as soon as possible because the RESTORE chain is broken and you need a new baseline for log backup.
– *Ask to double-check application consistency* because data recovered could be NOT consistent at application level. (we have done an uncomplete recover). If applicaton checks fails and nothing is fixable rapidly at application levele you have to consider, at the end, only a complete restore.