Every now and again I hear or read that the only file extensions for a SQL Server database are mdf, ndf and ldf. Not true I’m afraid. And I truly wish it was. The file extensions can be whatever you want, or even nothing at all.

Note: Here are the common (recommended) usages.

mdf – Used for the primary data file.

ndf – Used for secondary data files.

ldf – Used for the log file(s).

Quick example for proof that you don’t actually have to use these file extensions.

CREATE DATABASE [BadFileExtentions]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'BadFileExtentions', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\BadFileExtentions' , 
SIZE = 5120KB , FILEGROWTH = 1024KB ),
( NAME = N'BadFileExtentions2', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\BadFileExtentions.jpeg' , 
SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'BadFileExtentions_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\BadFileExtentions.bak' , 
SIZE = 1024KB , FILEGROWTH = 10%)
GO

So what does this mean? Should we start naming our database files whatever we want? No, absolutely not! It means that you need to be extra careful when specifying the name of the files. You really don’t want to use non-standard file names. The confusion! The misunderstandings! Do you really want the operating system to think your database file is a really big picture? Or even worse (and while I realize this sounds like a stretch I’ve had it happen) you accidentally give your file a .bak extension. Then one day your automated process that deletes old bak files runs as the same time your instance is down. Bye bye database file.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: database files, microsoft sql server