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.

Can a Database Have Multiple Files with Extension MDF? - Interview Question of the Week #279 ExtensionMDF-800x278

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:

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

, ,
Previous Post
How to Alter Index to Add New Columns in SQL Server? – Interview Question of the Week #278
Next Post
How to Force Index on a SQL Server Query? – Interview Question of the Week #281

Related Posts

1 Comment. Leave new

  • Something to add to this – you can actually have a database with 0 MDF files too. It is just a file name convention, but SQL Server doesn’t require you to put a file extension on the data files.

    I would not recommend doing this, but you CAN do it.

    Reply

Leave a Reply

Menu