Thursday, February 7, 2013

Need to attach an mdf file without transaction log file in SQL server

If someone has deleted your transaction log file intentionally and you have only .mdf file of your database then you can still attach your mdf file with the database. No matter your database was cleanly shutdown or not. In this article I will thoroughly guide you methods & steps which are required to recover your database when you have only .mdf file of the database.

Database was cleanly shutdown: If your SQL server database was cleanly shutdown then you can attach your database with the help of SQL server management studio (SSMS).

Let’s discuss all the steps in details:

  • 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.

  • Click on the Add button from appeared dialog box.

  • Now a Locate Database Files dialog box will be appeared.

  • Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
  • 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. 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.
Database was not cleanly shutdown: Try below steps if your database was not cleanly shutdown.
  • Create a same size database
  • Now shutdown the SQL server
  • Change the old mdf file with new one
  • Start the SQL server, your database may go in suspect mode
  • Now change your database status from suspect mode to emergency mode by ALTER Database command
  • Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair
Note: You may loss some amount of data but your database will be available again.