2

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 ?

Mehdi Souregi
  • 3,153
  • 5
  • 36
  • 53
  • What did you research and find to not work before posting this question? – dfundako Dec 16 '16 at 14:36
  • 1
    I did some search but I did not find my answer, besides i have a 2TB database so i can't do some risks. – Mehdi Souregi Dec 16 '16 at 14:41
  • 2
    You better plan on doing this in a test environment first. Production is NOT the place to try out an approach to something you found on the internet. – Sean Lange Dec 16 '16 at 14:54
  • Yes you are right, lucky i am , i did a backup DB and tried something that did not work on the backup DB, i was going to loose all my data :) , the only issue with this, is the time to create another backup DB. So i am just here asking if there is a guaranteed working method, and i wonder why microsoft did not create at least a documentation about this issue. I mean when you create a database you can create as much .ndf files as you want,right ~for performance purpose~ but they did not think about the option of merging those files into one single file. – Mehdi Souregi Dec 16 '16 at 15:09

2 Answers2

5

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
Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
1

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.

Mehdi Souregi
  • 3,153
  • 5
  • 36
  • 53