How to Merge MDF and NDF Files in SQL Server Database?

In the case of SQL, users often create separate MDF & NDF files. And when they don’t need two different files, they get stuck with them. This is why they want to learn how to merge MDF and NDF files in SQL Server database without any hassles. Well, in the modern technological era, sometimes it becomes quite necessary to make sudden changes.

There can be various reasons behind such a decision. Therefore, this article is going to explain some of the most common user queries, reasons, regarding the same. Moreover, we are going to mention the two methods including manual & advanced ones for this NDF & MDF merging or attaching operation.

User Queries to Merge MDF and NDF Data Files in SQL Server

We have plenty of emails where users have asked us for the solution for this complicated-looking problem of having MDF & NDF files in the database. Now, in order to get the best results without any errors, users need to first focus on user requirements. For that, we have mentioned one of the queries we received in our mails & some queries from public platforms.

“Hello sir, it’s been 3 years now that I’m using SQL database for my local business. I started with keeping only the MDF file but after some time, we started having NDF files as well. Now that our business is growing, we want to merge or combine the MDF & NDF files if possible. This way, it’ll be easier for us to manage it & focus primarily on business.”
– Emmanuel Glover

Just like Emmanuel, we have a lot of such users asking their queries on public platforms as mentioned below:

All Reasons to Know How to Merge MDF and NDF Files in SQL Server

Now, we know that users are trying to find a solution for this so here we pass our level one which acknowledges the issue. Another crucial stage is to find the root cause for it. To put it simply, finding the reasons will help users understand the severity & challenges of the task.

Ease of Use – The very first reason is for easier management. Yes. Users often require a single file containing all the information to make the management simple.

Storage Issues – Some users prefer one file in order to keep the storage less. However, the file structure remains the same. It’s only the data inside which determines the storage space.

Testing & Learning Purpose – A lot of users want to merge the MDF & NDF files for testing & experimenting purposes. It is a great way to learn about such files in depth without errors.

Legacy Practices – Some users& organizations keep following their legacy. What once they have been taught, makes their file structures complicated which needs to be corrected.

Lack of Knowledge – Some users misunderstand the performance of the database. They always get confused if they should have two files or one to get optimum performance.

How to Merge MDF and NDF files in SQL Server Database Manually?

The manual process here is not a direct one. It is a combination of some different processes that result in the same as the user wants. However, there is no surety for users to get the expected results.

For this, users must be proficient in manual commands like DBCC SHRINKFILE with EMPTY option.

Step-1. Firstly, users need to do a Full SQL Database Backup process.

Step-2. Run the below CMD to move NDF file data file to other data files.

DBCC SHRINKFILE('Logical_NDF_File_Name',EMPTYFILE)

Step-3. Now you execute this command to remove the NDF file.

ALTER database <Database_Name> REMOVE FILE 'Logical_NDF_File_Name'

Now, users need to execute the same step again & again for all the NDF files to get the result. The reason experts do not opt for this solution is because of being quite lengthy. Moreover, there are schances of complete data loss or severe corruption as well.

How to Merge MDF and NDF Files in SQL Server in 5 Steps

Instead of the manual solution, users must opt for the advanced SysTools SQL Database Recovery Tool which can easily merge the primary& secondary database files in just 5 simple steps. The best thing about this is that there is a demo version that is free to test.

Download the utility & then follow the below steps in order to get the perfect results.

Step-1. Launch the Software & Click on the Open button.

Step-2. Select the MDF File from the System to add here.

Step-3. The tool detects the corresponding NDF File also.

Step-4. Wait for the Tool to Scan these files.

Step-5. Click on Export to get MDF file with combined data.

 

Now, after this, users can have the MDF files with the merged data from the NDF files as well. This is how the software allows users to combine MDF and NDF files in SQL Server database with ease.

Automated Solution Features to Get Endless Benefits

Now, the reason for users to select the automated solution is not just because of its capability to merge MDF and NDF files in SQL Server. But there are plenty of other features as well that help users in many ways. Some of these features are:

• Repair Damaged Data: The software is very well capable of repairing the damaged or corrupted data of different levels from the files including virus-affected files.

• Recover Deleted Data: Not just repair but recovery is another crucial aspect of this tool. It can also help users get back the deleted data from the SQL server database.

• Merge MDF & NDF Data: Another feature is what we are searching for right now about the MDF & NDF merging process & yes it is easily possible here.

• Auto Detect & Scan Files: It’s quite easy for users to auto-detect the SQL Server version, MDF, and NDF files & scan them for minor or major level corruption.

• Export Data in Various Formats: Users can easily export the data in various formats like CSV, SQL scripts, directly to SQL Server, with schema only or data.

• Fixes Ransomware Affected Files: The software is smart enough to repair the files affected by the ransomware virus with ease. However, the complexity also matters in this case but yeah, this tool has done it several times.

The Final Say

After analyzing all the crucial facts, it’s time that we end this article. Using the above solution, almost all the users know how to merge MDF and NDF files in SQL server database. This data-combining process is easy with the automated software. While the manual one can take time & even possess some errors.

Users must choose their solution with the utmost attentiveness in order to get accurate outcomes later without any hassles.