Know How To Attach MDF File Without LDF File

Stephen West | July 25th, 2017 | SQL Master Database |

SQL Server uses two files to save its database – MDF files and LDF files.

MDF File: MDF file is also known as a primary data file that contains all the data and schema. The supported file extension for MDF file is .mdf.

LDF File: LDF stands for Log data file and contains all the database transaction logs. The file extension to save the LDF file of SQL Server is .ldf.

User can also create a secondary database file to store the data which is known as NDF file if the primary database file become large.

Need to Attach MDF file Without LDF File

If you are getting an error message while attempting to attach SQL Server database files, it means your SQL Server database files have surface corruption issues. You cannot take the log backup, if there is a corruption in your LDF file. In this situation you can attach your MDF file without LDF file and recreate your LDF file. This can also resolve your problem if your log files are deleted intentionally or intentionally.

How to Attach MDF file Without LDF File?

There are mainly two methods to attach SQL MDF file without LOG file.

  • By using SQL Server Management Studio (SSMS)
  • By using T-SQL script

Attach MDF File Without LDF file by Using SSMS: You can attach your SQL MDF file without LOG file by following underlying steps.

  • Open MS SQL Management Studio
  • Attach MDF File Without LDF File

  • In Object Explorer , Right Click on Databases and then Click on Attach from the drop down menu
  • Click On Attach

  • In Attach Databases Windows, Click on Add button
  • Click On Add

  • Browse the location of your MDF File in Locate Database Files, select the file and click on OK
  • Attach MDF File Without LDF File

  • You will see the database details in Attach Database dialog box; Select the LDF file and then Click Remove Button. Now Click on OK to attach MDF file without LDF file. While attaching the database, SQL Server will create a new LDF file.
  • Attach Database Window

  • Now you can check your database in the databases folder.

Attach MDF File Without LDF file by using T-SQL script: You can also run a TSQL Script on SQL Query to attach your MDF file and recreate your transaction log file.

Attach MDF Without LDF File T-SQL

Where, testdb is the name of your database.

Now you can check your database in the database folder.

NOTE: These methods will work only if your database is cleanly shut down and you have a healthy MDF file database.

Verdict:

In this Blog you have seen two methods to attach MDF file without LDF file. One is by using SQL Server Management Studio and another by using SQL Script. Both the methods will works only for the healthy MDF file and in case on clean shut down of database. In case, if the database file you are trying to attach is corrupted, then first try to restore from a recent backup of your database file or there are some alternate solutions like SQL MDF recovery Software which is capable to repair corrupt .mdf file and after fixing the corruption issue, you can follow the above approach to attach .mdf in SQL Server.

Free Download
100% Secure

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.