Ever since anyone starts learning Microsoft SQL Server, a misconception sets in. The misconception is that data and log files can be defined as under:

1. File with extension – .mdf – is a Primary data file
2. File with extension – .ndf – is a Secondary data file
3. File with extension – .ldf – is a log file

These extensions are the default extensions that are used by Microsoft SQL Server for the default system and sample databases available. Default extensions are good because it helps us to set some sort of uniformity across multiple Microsoft SQL Server environments.

But, the situation is more like the difference between a “recommended configuration” and “minimum configuration”. MDF, NDF and LDF are recommended configurations. But, SQL Server does not really care about the file extension. Here’s a little demo (while this demo shows screenshots of SQL 11 (“Denali”) CTP01, this is also valid for previous releases of Microsoft SQL Server):

/*************************************************************************************
CREATE A DATABASE SUCH THAT:
Primary Data File Extension   - prf
Secondary Data File Extension - sdf
Log File Extension            - dbl

NOTE: This script is provided "AS IS" and without Warranty. This script is for 
      demonstration purposes only.
*************************************************************************************/
CREATE DATABASE [ExtensionTestDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'E:DatabasesDB_DEFAULT.prf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB), 
    FILEGROUP [DB_DATA_FG](NAME = [DB_DATA], 
                           FILENAME = 'E:DatabasesDB_DATA.sdf', 
                           MAXSIZE = UNLIMITED, 
                           FILEGROWTH = 102400 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'E:DatabasesDB_LOG.dbl', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB) 
    COLLATE SQL_Latin1_General_CP1_CI_AS
GO

When we run the above script to create a new database, we find that the database creation is successful. In a new Query window connecting to the same SQL Server instance, we can fire a query using the sys.sysfiles catalog view to confirm that the SQL Server is indeed okay with the non-default extensions.

USE [ExtensionTestDB]
GO
SELECT * FROM sys.sysfiles

I hope that this post helps clear out the misconception around the default primary/secondary data and log file extensions.

Until we meet next time,

Be courteous. Drive responsibly.