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
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings.
Restore backups in SQL Server 2000 and SQL Server 2005 with this command.
Back up tables from a SQL Server database to a file with these commands.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.