In general if you have a crashed server and need to bring up your databases on a new server you want to restore from backup not copy and reattach the mdf files. The crash could have caused corruption that will not exist in the backup.
You can restore your system databases as well although it isn't always recommended. If you are going to you need to make sure your new system is as close to the old as possible. Particularly the patching level for SQL Server.
If you do not have backups, and you absolutely have to have your system databases from the old server then you have two options. You can either attach as a different dbname and copy information over (msdb and model only since it won't work for master). Or you can tell your instance that the new location of the system database is actually the correct one.
So in your case you would copy the mdf & ldf msdb files to your new server. Again make sure your patching is the same as the old server. Then follow the instructions in this link to "move" your msdb files. In other words you are going to tell it that the files from the old server are actually the correct ones. Once done you will need to re-start your new instance. Then make sure you keep the original msdb files from the install just in case.
Of course the other way to "move" your msdb files is to shut down your instance, move/rename the installed msdb files and copy in your old msdb files making sure they are in the same location & have the same name. Then restart your instance. If anything goes wrong you can simply move the installed msdb files back into place (while the instance is shut down) and try a different method.
But recently I came across some database on a server where every database had multiple mdf files.
That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many data file with .mdf
extension but only one will be primary data file. It is better to give mdf
extension to primary data file and ndf
to secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.
Is there any wisdom in having multiple .mdf files for a database?
If you mean:
Is there any wisdom in having multiple primary files for a database?
The answer is no, a database can have only one primary data file.
But if you mean:
Is there any wisdom in having multiple data files (.mdf, .ndf, or differently named) for a database?
It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.
As per this MSDN BOL article
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive
I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group
Best Answer
Yes, you can go ahead with that.
Limit the other secondary files (Disable Autogrowth) and create new ndf file in the new drive. This will resolve your space issue.