Best Way to Attach MDF Without LDF File in SQL Server

Sometimes users may encounter a condition where they do not have a full database backup and the LDF file also becomes corrupted due to abrupt power loss. Or, someone intentionally deleted the secondary database file and now, users are left with only a master database file. Now, the question is can we attach MDF without LDF file? If so, then how?

To answer such questions, we are here with this post. However, before directly proceeding to the solution section, let us take a real example:

Database Name: Testdata

Data File:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testdata.mdf

Log File: Not Available

SQL database is one of the most common databases used for every type of business and customer accounting. It is widely used in business platforms for the purpose of data analysis, processing of the components and data integration. The database of SQL server comprises three file types:

1. Primary Database File (MDF): Master Database Files (MDF) are primary database files, where all the information of database is stored. Data created by the user is saved in ‘Tables’, which has 2-dimensional structures (Rows and Columns). Users can create many MDF files in the database of SQL server. Extension of MDF files is .mdf.

2. Secondary Database File (NDF): These files are user-defined and optional for store data. It can be used to spread data along multiple disks by putting each file in the different disk drive. When MDF database exceeds its maximum size, then users can use NDF files. The extension of Secondary database file is .ndf.

3. Log File (LDF): The transaction log files stores all transactional information of SQL database. There must be at least one log file in each database. The log information hold by LDF file, help to recover log information. The file extension of LDF file is .ldf.

Techniques to Attach MDF File Without LDF File

We can attach SQL MDF without Log file and there are multiple manual ways to do so.

Method 1. Attach MDF File Using SQL Server Management Studio (SSMS)

Follow the steps mentioned below for attaching the database without LDF File:

• Launch MS SQL Management Studio

• Now, in the Object Explorer tab, hit a right-click on the Databases option. Highlight Attach option from the drop-down menu list.

• Doing this will appear Attach Databases wizard. Here, hit Add button.

• Select the MDF file from Locate Database Files window and click OK.

• You will get the database details in Attach Database wizard. From here, choose the LDF file from list and hit Remove button. Next, click OK for attaching MDF file without LDF.

• After this, while you will try to attach the database, SQL Server will generate a new Log file.
• To check if the MDF is attached, check your database in databases folder.

Method 2. Attach MDF File without LDF File Using T-SQL Script

One can run a T-SQL Script on SQL Query to attach the Master Database file and recreate the log file. For this:

• In Server Management Studio toolbar, click on New Query
• Then, execute the following query:

CREATE DATABASE testdb ON
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\testdata.mdf')
FOR ATTACH_REBUILD_LOG
GO

Note: Here, testdb is the name of database and it can be varied according to the name of your database.

Once the query is executed successfully you can check your database from the database folder.

What if MDF File Also Gets Corrupted?

SQL Database Recovery Tool is an expert utility to repair corrupt MDF and NDF files of SQL Database. It has the capability to recover tables, stored procedure, views, functions, triggers, keys. Also, it supports both ANSII and UNICODE XML data types and recovers MDF files data affected by Wallet Ransomware. One can scan and repair multiple NDF files using this software.

Download Now Purchase Now

Closing Words

After taking an overview on SQL Server database files, we have discussed the methods to attach MDF file without LDF file. Users can execute this task in two different manners if their MDF file is in healthy condition as mentioned above. Otherwise, a reliable third-party software is recommended here in case if MDF file gets corrupted.

Frequently Asked Questions