How To Import MDF File to SQL Server Database Without LDF

This blog is composed to present the multiple methods to import MDF file to SQL server database without the LDF log file when the MDF file is in a corrupt or missing state.

As users know, SQL Server is a famous application for storing and managing large amounts of data, has database files to store its information. Basically, it has two types of files. The MDF files are the primary files of the SQL server database which are used to manage the schema and data. Other than this database file, the LDF file is the Log Database File, which stores transactional or log information of the SQL database. Furthermore, the LDF files can also be used for checking the logs in case of corruption. Sometimes, these log files get missing while attaching the MDF file to the server, and the process fails due to this. In such circumstances, one job can be done – attach MDF without LDF log file. After the MDF file is successfully added, log files would be generated automatically.

Now, let’s learn the cases in which the LDF files are missing.

Why Add MDF file to SQL server Without LDF?

When the user try to attach or import MDF files in SQL and if you are getting an error message while attempting this process, it means your SQL Server database files have some corruption issues. You cannot take the log backup if there is corruption in your LDF file. As your LDF files may be missing due to corruption. In this situation, you can attach your MDF file without the LDF file and restore the MDF file SQL server. This can further resolve your problem if your log files are deleted intentionally or unintentionally.

So, in the below section, I am going to discuss various techniques to attach MDF files to SQL server.

DIY Techniques To import MDF File to SQL Server without LDF

There are primarily two techniques to attach SQL MDF files without LDF manually.

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

Technique 1: Use SQL Server Management Studio

It is an easy method and does not need any technical skills for its achievement. The SQL server interface allows SQL administrators and authorized users to attach MDF files and remove corrupt or missing (not found status) log files. Let us make it more precise with the steps and screenshots.

• Run the SQL Server Management Studio. Navigate the left panel in the Object Explorer, choose Databases, and right-click on it. Then click Attach.

• Next, a new page named Attach Databases will get open. Hit Add to add the SQL database file.

• Now browse and select the .mdf file from the system folder location. Click OK.

• Very soon the selected MDF file will get added, and you can see the log file also along with but in the “not found” state. Choose the log file and click the Remove option to delete this log file.

• After that, the log file will get removed. Click the OK button to import the added MDF file only.

Also Read – How to Fix Recovery Pending State in SQL Server Database

Technique 2: Use T-SQL Script

With basic knowledge of SQL scripts, you can try to add the SQL database again without the Log (.ldf) files. To do so follow the below directions carefully.

• Start the Microsoft SQL Server Management Studio. Right-click on the SQL Server directory node and select the New Query option from the list.

• In the SQL query box type this T-SQL script to attach database SQL server.

CREATE DATABASE <mdf file name> ON

(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Template Data\.mdf‘)

FOR ATTACH_REBUILD_LOG

GO

• After that, click Execute at the top.

• The results would be shown like this.

This technique needs some technical skills and prior knowledge of running scripts. 

What to do if MDF files get Corrupt?

The techniques mentioned above do not work if your database MDF file is corrupted. In such circumstances, it is recommended to use the SQL Database Recovery tool. The software can efficiently scan and repair corrupt database files in just a few simple clicks. After the file is repaired successfully, you can easily import the MDF file to the SQL server database environment. Moreover, the tool supports repairing database MDF & NDF files of SQL Server 2000, 2005, 2008, 2012, 2014, and 2016. 

Follow the Steps to Repair SQL Database and restore MDF without LDF:

• Download & launch DRS SQL Recovery software on your machine.
• Select the open button to navigate corrupt MDF files for recovery.
• After that hit the browse button to add files.
• Choose the MDF files from your computer and click open to add them.
• Here pick the recovery mode from the available options.
• Select the checkbox if you require to recover deleted records.
• Also, tick the check box to recover advanced deleted records.
• Choose the Auto-detect option to detect the collation type automatically, or choose the Manual option to define the collation type manually.
• Here enter the log file path and click Ok.
• Now after choosing the path, click OK to start the scanning process.
• Very soon the software begins with the scanning process.
• Now click OK after the scanning process gets completed.
• After that select, the MDF file items in a tree structure that you want to recover
• Hit the Save button on the taskbar to start the saving process of MDF files.
• Choose the saving mode: From the SQL Server database or SQL Server Compatible Scripts
• Now click Ok to start the recovery process and wait till the saving process gets completed.

Verdict:

In this technical essay, you have noticed different methods to attach MDF file without LDF file. By using SSMS – SQL Server Management Studio), T-SQL Script, and repairing SQL database using an automated tool. The DIY methods will work only for the unharmed MDF file. But 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 fixed then it is recommended to use the alternate solution that is able to repair and import MDF file to SQL Server easily.

Related Post