Home » SQL Master Database » Know How To Attach MDF File Without LDF File

Know How To Attach MDF File Without LDF File

Stephen West | Modified: 2021-07-22T16:33:42+05:30|SQL Master Database | 4 Minutes Reading

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 some 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 unintentionally.

How to Attach MDF file Without LDF File?

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

  • Restore MDF database in SQL Server using SQL Server Management Studio (SSMS)
  • Restore MDF database in SQL Server using T-SQL script

Attach MDF File Without LDF file by Using SSMS: You can attach your SQL MDF file without LDF 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 to import MDF file in SQL server.

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 restore MDF database in SQL Server 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 database file.

What to do if Database MDF files get Corrupt?

The methods mentioned above do not work if your database MDF file is corrupted. In such cases it is suggested to use the best SQL Repair tool. The tool can efficiently scan and repair the corrupt database files in just few simple clicks. Once the file is repaired successfully, you can easily import MDF file in SQL server to live SQL Server environment. More so, the application support to repair database MDF & NDF file of SQL Server 2019, 2017, 2016, 2014 and all its below versions.

This is what you need to do to retrieve data from corrupt MDF file

  1. In the very first step, download and run the tool in your system.
  2. After that click on Open to add the database MDF file into the tool.

Import MDF file in SQL server

2. Here, you can select a scan mode as per the corruption level. For minor corruption choose Quick mode and Advance mode to repair severely corrupted database and recover deleted items.

fix Suspect Database in SQL Server

3. Once the file is scanned successfully, the tool provide a preview of complete data stored in the MDF file. To extract data from MDF file click on the Export button.

Recover Deleted Functions And Views in SQL Server

4. Now, set Export to/As as SQL Server Database and provide the required details for authentication.

Attach MDF File Without LDF File

5. Choose Export your data with the only schema or with Schema and data as per your desire and hit Export button.

Attach MDF File Without LDF File

Also Read: How to Fix Metadata corruption?

Verdict:

In this Blog you have seen different methods to attach MDF file without LDF file. By using SQL Server Management Studio, SQL Script and using an automated tool. The manual methods will works only for the healthy MDF file and in case on clean shut down of database. In case, if if the database file you are trying to attach is corrupted, then first try to restore from a recent backup of your database or if the problem is not resolved then it is suggested to use the alternate solution which is capable to repair and restore MDF database in SQL Server easily.