SQL Server – How to attach database without LDF file

There can be a situation where you don’t have full database backup and you have corrupted your ldf file due to power shutdown (or some one intentionally deleted the ldf file). And you are left with only the data file (i.e mdf file).  You can still attach your mdf file with database and recover it.

Below is an example of how, you can attach a database without ldf file.

Database Name : Northwind

Data File:              C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.mdf

Log File:                Not Available

 

Using SSMS : Attaching database without LDF File

  • Connect to the SQL server instance using SSMS
  • From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
  • Now click on the Attach tab.

 

2

 

 

  • Click on the Add button from appeared dialog box.

3

 

 

  • Now a Locate Database File, select it and click ok. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted.

 

 

4

 

  • Now if you click OK, it will fail with the operating system error ( unable to open the physical file).

 

 

5

 

 

6

 

  • To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
  • Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.

 

7

 

 

 

9

 

 

Hope this was helpful !!!