Q
Problem solve Get help with specific problems with your technologies, process and projects.

Combine MDF and NDF into a single MDF

I currently have a Windows 2000 server with a 300GB+ SQL database that has four NDF data files. I will be moving this database to a Windows 2003 server and now have no need for multiple data files (MDF). Is it possible to combine the MDF and NDFs into a single MDF?
Yes, this can be done using the DBCC SRINKFILE command using the EMPTYFILE option. This will remove the data from the data file specified to the other data files. You should first make the MDF data file large enough to store all of the data. Then remove the auto grow options for the three NDF data files. The DBCC command would need to be run for each of the three data files. Since this is a very large database, this process will take some time. When this is complete, you can use the Alter Database command to drop the three NDF data files.


Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on SQL Server Backup and Recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close