How to recover or attached a database which transaction log (.LDF) file is missing or corrupt?

• There might be a situation due to some reason the transaction log file is corrupted or removed , 

But we can try to recover the database using the data file(.mdf).

• In the script below we will create scenarios and will recover the database using only the data file(.mdf).

—Create Sample Database

Create database Demo_DB

—-Find the location of the DB

sp_helpdb ‘Demo_Db’

—c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db.mdf
—-c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db_1.ldf


—Take database in single user mode & detach the database using below script———


USE [master]
GO

ALTER DATABASE [Demo_Db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO


USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’Demo_Db’
GO

—–Delete the log file from drive location c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db_1.ldf

—attach the database using data file .MDF file only SQL Server will create the log file itsself————————

USE [master]
GO
CREATE DATABASE [Demo_Db] ON
( FILENAME = N’c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db.mdf’ )
FOR ATTACH
GO


———You will get below message after the command execution———–


File activation failure. The physical file name “c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db_1.ldf” may be incorrect.
New log file ‘c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADemo_Db_log.LDF’ was created.