Learn How to Attach SQL Server Database Without Transaction Log File

Ashwani Tiwari | Published: September 24, 2020 | SQL Server

Summary: Are you looking for a solution to attach SQL database without transaction log file? If yes! Then you have landed on the right page. In this write-up, we have described two simple approaches that you can adopt to perform this task quickly.

 

SQL Server is a popular relational database application used to store and retrieve data according to the requirement of other applications. It uses two files to store its database – MDF files and LDF files.


MDF file (Master database) is also known as the primary file of database which is used to store schema and data. The supported file extension for the Master database file is .mdf. On the other hand, LDF stands for Log data files that contain all the database transaction logs and also be utilized for checking the logs in case of corruption. The file extension to save the Log file of SQL Server is .ldf.

 

Let’s see in what circumstances the transaction log file is missing.

When users try to attach MDF file to the SQL Server database and then they receive an error message, which indicates there is an issue or corruption in the database. Hence, due to the corruption in database, the transaction log files are found to be missing and users are left with the master database file only. Another reason for the missing log files is accidental deletion.


Therefore, in such scenarios, where transaction log files are missing, users can still restore the database by attaching the MDF file to SQL Server without LDF file.

So let’s get started with the solutions,

Methods to Attach SQL Server Database without Transaction Log File

In this section, you will get the different methods by which you can attach MDF files to SQL Server in the absence of log file.

1. Using SSMS
2. Using T-SQL script Method
3. Using an Automated software

Here, it is important to mention that the manual methods will work only if the database is cleanly shut down and the required database is corruption-free.

1. Attach SQL Server Database Without Log File – Using SSMS

Follow the steps to attach MDF file without LDF file

1. Open SQL server management studio.
2. Under the Object Explorer, right-click on the Databases and then choose the Attach option from the drop-down menu.
3. Under Attach Databases Windows, click on the Add button.
4. Browse the location of MDF file, select the file that you want to attach, and then click on OK.
5. Now, you will see the database details in Attach Database Windows, it shows the Log file is not found.
6. Select the LDF file and click Remove button. Then click on OK to attach MDF file without log file.
Once you have successfully attached the database, SQL Server will create a new LDF file.

2.  Use T-SQL script Method

You can also run a TSQL Script on SQL Query to attach the MDF file without LDF. Use the script shown below:

USE Master
Go
CREATE DATABASE Database_name ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\’Database_name.mdf’)
For ATTACH_REBUILD_LOG
GO

3. Professional Method to Attach MDF File in SQL Server without LDF File

The above mentioned manual methods are successful when the MDF file is in healthy condition (not corrupted, damaged, or broken). In case, if you are trying to attach a corrupted database file, then you need to use an automated tool SQL Database Recovery which fixes all the issues and restore the database file (.mdf file) in just a few minutes.

Moreover, it can recover the permanently deleted table records & database objects and provide an option to export data directly to the SQL Server database or as SQL Compatible Script.

With the help of this software, you can also access the database content saved in the secondary database files (.ndf files).

Now, follow the below steps to attach MDF file in SQL Server without log file

Step 1. Download and Launch the tool in your system. Click on Open to load MDF file that you want to attach.

Open Database file to attach

Step 2. Choose the Scan mode (Quick or Advance) and then select the SQL Server version of the selected .mdf file.

select scanning mode

Step 3. Preview the database, which you want to attach and click on Export to start the exporting process.

click export to attach the SQL database without transaction log file

Step 4. Now, select Export to SQL Server Database option and fill Server Authentication like Server Name & choose Authentication mode either Windows Authentication or SQL Server Authentication.

select export to SQL database and provide Server credentials

Step 5. Choose either Create New Database or Export to Existing Database option to attach MDF file without log file.

choose export database option

Step 6. Select the database components which you want to attach and finally hit the Export button.

select database component that you to attach

Done, in a few simple steps you have completed the job.

The Final Words:

Well, in this above blog, we have discussed different approaches to attach SQL Server database without transaction log file. The described manual methods require technical knowledge and it may also lead users to permanent data loss. Thus, users can opt for the automatic technique for quick and 100% accurate results.
As you can conclude from the above steps, you only need to load the MDF files into the software and the rest process is automated.