I have a database in SQL Server 2005 and it contains two filegroups using two different .mdf files. I would like to merge these and use only one .mdf file for the database. Any help on how this can be done?
This is pure spitballing, so act accordingly. Assuming the tables in both filegroups are identical, you could define a partition to include both filegroups. Merging the partitions will give you all the data in one filegroup.
If the tables are not identical, you'd have to reassign them individually from one filegroup to the other.
Either way, you eventually have one full filegroup and one empty filegroup that you can delete (first verify it's empty!).
Having two filegroups is not normally a problem. That said, you would have to identify which filegroup is to be removed. LOG and PRIMARY can not be removed, as they are system generated.
Next you would have to identify all the tables and/or indexes that are in the filegroup to be removed. Script out the indexes, alter the script so that they are all created on the filegroup you want. Drop the indexes, then run the script. Don't forget to include primary key and unique constraint indexes.
For heaps (tables with no clustered index), you can export the data to text files with SSIS, or BCP. Drop the existing tables, recreate them in the proper filegroup, then re-import the data.
After all of that, you should have all objects in the filegroup you want. Whether it is worth it, is another matter.