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.









