Is it possible to merge ndf files and mdf file into one single mdf file?

Question

I have an .mdf file, and two .ndf files of the same mdf file. Is it possible to merge those files into one single .mdf file without loosing any data ?

Answer 1

Yes,you can ..use DBCC SHRINKFILE with Empty option

From MSDN:

EMPTYFILE:(Emphasis in bold Mine)

Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. Emptyfile assures you that no new data will be added to the file.

The file can be removed by using the ALTER DATABASE statement.

Example:

DBCC SHRINKFILE (nameofdatafile, EMPTYFILE);  
GO  

-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  
GO  

Answer 2

Thanks @TheGameiswar for the answer.

If you have for example file1.mdf, file2.ndf and file3.ndf

The way to merge them in one file :

DBCC SHRINKFILE(file2, EMPTYFILE);
GO

DBCC SHRINKFILE(file3, EMPTYFILE);
GO

And then you can remove them safely :

ALTER DATABASE MyDB
REMOVE FILE file2;  
GO  

ALTER DATABASE MyDB
REMOVE FILE file3;  
GO  

file1 will contain the whole data after that.