Does the SQL Server Database Filename Matter?

Do you know the basics of how to create a database? Hopefully you do and can do so without the GUI. However do you know the extensions are for database files? As of SQL Server 2012, these are the extensions:

  • Main data file – .mdf
  • Secondary data files – .ndf
  • Transaction Log files – .ldf
  • Full backup files – .bak
  • Differential backup files – .dif
  • Transaction Log backup files – .trn

However these are merely suggestions, and dictated by convention. In fact, in the Files and Filegroup Architecture page, BOL says that the “recommended” extensions are those I’ve listed for different types of files. For backups, these aren’t documented since you can actually include different types of backups in the same file (Don’t do this).

Here’s a quick test:

( NAME = N'NameTest1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest.mdf' 
, SIZE = 2 )
( NAME = N'NameTest1_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest_log.mdf' 
, SIZE = 1 )

If you notice, I’ve created a database with one data file and one log file, both using the extentions “.mdf”. This works fine and the database is usable.

I can do the same thing with ldf.

( NAME = N'NameTest2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf' 
, SIZE = 2 )
( NAME = N'NameTest2_Data2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_data.ldf' 
, SIZE = 2 )
( NAME = N'NameTest2_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_log.ldf' 
, SIZE = 1 )

In this example I even added a secondary data file. If I check the physical file locations, I see the files I created.


Note that Explorer sees these as the type of file based on the extension it has associated with that filename, but that doesn’t affect how SQL Server uses the files. If I look in the properties for the database, I see the files listed as expected.


These don’t affect the operation of SQL Server or the database at all, however they can be confusing for DBAs. I recommend that you stick with the customary extensions for SQL Server files.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to Does the SQL Server Database Filename Matter?

  1. One thing that could have an effect if you opt to use a nonstandard file extension is anti-virus. Your anti-virus (especially if it comes from MSFT) may be configured to ignore LDF, NDF, and MDF by default, but if you choose a different extension, you may find your files locked up by AV when you reboot.

Comments are closed.