Can a Database Have Multiple Files with Extension MDF? – Interview Question of the Week #279

Question: Can a Database Have Multiple Files with Extension MDF?

Answer: Yes. It is possible to have multiple files with extension MDF for a single database.

If your answer was yes. You can stop reading this interview question here. However, if your answer was no, I suggest that you continue to read ahead.

I read quite often on the internet that one should have one file which is a primary data file with the file extension of .mdf and all the secondary files should have an extension of .ndf. Well, in principle I agree with this as a best practice. However, the real world does not always follow the best practices.

While it is not recommended in the best practices, it is totally possible to have a single database with two MDF files. Here is how you can do it.

CREATE DATABASE [SQLAuthorityDemo]
ON PRIMARY
( NAME = N'SQLAuthorityDemo', FILENAME = N'd:\data\SQLAuthorityDemo.mdf' ),
( NAME = N'SQLAuthorityDemo1', FILENAME = N'd:\data\SQLAuthorityDemo1.mdf')
LOG ON
( NAME = N'SQLAuthorityDemo_log', FILENAME = N'd:\data\SQLAuthorityDemo_log.ldf')
GO

The script above will create a database with two MDF files and it will work just fine. Please remember that Microsoft says that every database has one primary data file but it does not mean it can have only one “file with MDF extension”.

Honestly, you can give any file in the database for any extension. All the operations will just work. You can even rename your database files with pdf or jpg extension but if they are valid database files, they will work just fine. The matter of fact, if you due to any reason, if you forget to give any extension to the file, it will still work as well.

Not only for data and log files, but it is also equally true for the backup files as well. There is no need to rename your database extension with .bak or .log extension. You can just create them with them an extension and it will just work.

Now remember, by not following naming standards, you may end up creating confusion for the end-users. It is always a good idea to give proper names and extensions to your database files.

Here are a few related blog posts:

• How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208
• SQL SERVER – Move Database Files MDF and LDF to Another Location
• SQL SERVER – How to Rename Extention of MDF File? – A Simple Tutorial
• How to Attach MDF Data File Without LDF Log File – Interview Question of the Week #078
• How to Move SQL Server MDF and LDF Files? – Interview Question of the Week #189
• SQL SERVER – Multiple Log Files to Your Databases – Not Needed
• SQL SERVER – Attach mdf file without ldf file in Database
• SQL SERVER – Can Database Primary File Have Any Other Extention Than MDF

Reference: Pinal Dave (https://blog.sqlauthority.com)