We help IT Professionals succeed at work.

Merging database files

ykchakri asked
Hi Experts,

Is it possible to merge 2 datafiles (test.mdf, test1.ndf)in to one. Due to in-sufficient disk space, I have created test1.ndf on another partition, so that the database 'test' could grow. Now that I am moving this database to another server, where I have plenty of disk space, I want to have all the data into one single .mdf file. How can I do that ?
Watch Question

1.Create a new database (with no objects) on your new server with a single file.
2.Generate the SQL Scripts from the existing database.
3. Run these scripts on your new database
4. Use DTS or BCP to get the data from one server to the other server.
    (   file_name ,EMPTYFILE )

Use your database file (.ndf) in the file_name place and use EMPTYFILE option in the bracket which will move all data from ndf file to mdf file. After empty of file, you can execute the command

Alter database
REMOVE FILE file_name

This will work.


Rename your old database (exec sp_renamedb ...)
Create your new database
Use the enterprise manager, expand server, expand database, right click on your old database, choose all tasks, choose export data.
Select the old DB as your source and the new DB as your destination. You can transport objects and data from one database to another.


Hi Mogaruna,

That works good for the files in the same file group. Now, I have a file in another file group. Is there any way that I can merge this file with the PRIMARY file group. I am thinking of dropping the objects in that file group and re-create in the PRIMARY. But, there must be an easier way than that.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.