How to Recover Database in case you have accidentally deleted Transaction Log File(.ldf) In MS SQL Server???

The best way to do this is to use the backups, and to have a backup strategy that allows you to recover database in the smallest amount of time and without any data loss.

But what if you don’t have any backup for that database?????? 

Lets create the same scenario then we will see the solution for same:

Create A sample database TLOG and a table with few records:

CREATE DATABASE [TLOG];
GO
USE [TLOG];
GO

CREATE TABLE DATA (
    ID INT ,
    NAME CHAR(20))
GO

INSERT INTO DATA VALUES (1,'VINAKR' );
INSERT INTO DATA VALUES (2,'BAGHEL' );
INSERT INTO DATA VALUES (3,'CHAUBEY');
GO

I’m going to start an explicit transaction and I'll update a record in table as below: 

BEGIN TRANSACTION;
GO
UPDATE DATA SET NAME = 'SACHIN' WHERE ID = 3;
GO

Now I’m going to force the data page holding the updated row to be flushed to disk:

CHECKPOINT;
GO

So Now we have an active transaction(uncommitted) , and the table modification has been written to disk.  Now I'm going to shut-down the server from another session: 

SHUTDOWN WITH NOWAIT;
GO

Now go to physical location of log files and just delete the transaction log file of TLOG.

Now Let's  start up SQL Server again. The database will not be accessible as log file is missing. You will get error as below:

 We can see the following in the error log:
======================================================
2014-08-23 13:38:15.82 spid21s     Starting up database 'TLOG'
2014-08-23 13:38:16.08 spid21s     Error: 17207, Severity: 16, State: 1.
2014-08-23 13:38:16.08 spid21s     FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2014-08-23 13:38:16.08 spid21s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.LDF" may be incorrect.

2014-08-23 13:38:16.08 spid21s     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.

========================================================

The database wasn't cleanly shut down and the transaction log isn't available so recovery was not able to run.
Let see the status of our database using sys.databases as below:

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'TLOG';
GO

It will return  RECOVERY PENDING. Below is the screen shot for same:

If the transaction was committed and SQL Server was cleanly SHUTDOWN in that case you can simply attached the MDF file and can rebuild your log  but as our database is in inconsistent mode we can not do that. Same is clearly mentioned in error log file as "The log cannot be rebuilt because the database was not cleanly shut down."

So, Now to recover our database first we will put it in EMERGENCY mode as below:

ALTER DATABASE TLOG SET EMERGENCY;
GO

You can see the database in SSMS as:

Now put the database in sigle_user mode and run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS:

ALTER DATABASE TLOG SET SINGLE_USER;
GO
DBCC CHECKDB (N'TLOG', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

It will executed successfully with below warning:

File activation failure. The physical file name 
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.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 'TLOG' 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.

Now check the database state again as below:

SELECT [state_desc] FROM sys.databases WHERE [name] = N'TLOG';
GO

state_desc
-----------
ONLINE

So the database is back online again because everything worked. Let’s see what happened to our table "DATA":

USE TLOG;
GO
SELECT * FROM DATA;
GO
==========================
ID NAME
1 VINAKR              
2 BAGHEL              
3 SACHIN               

(3 row(s) affected)

And the data is still corrupt – because even though the transaction log was rebuilt and repaired, the original transaction that changed the name to SACHIN never got a chance to rollback because I deleted the transaction log. 

So, This is how We can recover it but make sure this should be your last option to do the same.

Thanks For Reading this Post!!!