Search code examples
sql-serversql-server-2012mdf

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

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 ?

Solution

  • 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