What is an MDF file?

What is an MDF File? Understanding SQL Server's Primary Data Storage

Explore the core of SQL Server databases: the MDF file. Learn its structure, purpose, and how it interacts with other database files to store your critical data.

When working with Microsoft SQL Server, you'll frequently encounter files with the .mdf extension. These files are fundamental to how SQL Server stores and manages your data. An MDF file, which stands for Master Data File, is the primary data file for a SQL Server database. It contains all the essential startup information for the database and points to other files within the database.

The Role and Structure of an MDF File

Every SQL Server database must have at least one MDF file. This file serves as the central repository for the database's schema (tables, views, stored procedures, etc.), data, and other critical information. While a database can have multiple data files (including secondary data files, or NDFs), the MDF file is always the starting point for SQL Server to access and understand the database structure.

flowchart TD
    A[SQL Server Instance] --> B["Database (e.g., 'MyDatabase')"]
    B --> C["Primary Data File (MyDatabase.mdf)"]
    C --> D["Database Schema (Tables, Views, etc.)"]
    C --> E["User Data"]
    C --> F["Pointers to Secondary Data Files (NDFs)"]
    B --> G["Transaction Log File (MyDatabase.ldf)"]
    G --> H["Transaction History"]
    G --> I["Recovery Information"]
    style C fill:#f9f,stroke:#333,stroke-width:2px
    style G fill:#ccf,stroke:#333,stroke-width:2px

Simplified SQL Server Database File Structure

The MDF file typically stores the primary data groups and can also contain user-defined data. Its internal structure is complex, organized into pages, with each page being 8KB in size. These pages hold various types of data, including data rows, index information, allocation maps, and more. The first page of an MDF file is always the file header, which contains metadata about the file itself and the database it belongs to.

MDF, NDF, and LDF: The Database Trio

While the MDF file is crucial, it's rarely alone. SQL Server databases typically consist of three main types of files that work together:

• MDF (Master Data File): The primary data file, containing the main database structure and data.
• NDF (Secondary Data File): Optional files that can be used to spread data across multiple disks or file groups. A database can have zero or more NDF files.
• LDF (Log Data File): The transaction log file, which records all transactions and database modifications. This file is critical for database recovery and ensuring data integrity. Every database must have at least one LDF file.

Managing MDF Files

Managing MDF files involves several common tasks, such as attaching and detaching databases, backing up and restoring, and moving files. SQL Server Management Studio (SSMS) provides a graphical interface for most of these operations, but they can also be performed using Transact-SQL (T-SQL) commands.

-- Example: Attaching an MDF file to a SQL Server instance
CREATE DATABASE MyAttachedDB
ON
(FILENAME = 'C:\SQLData\MyAttachedDB.mdf')
FOR ATTACH;

T-SQL command to attach an existing MDF file as a new database.

-- Example: Detaching a database (which includes its MDF and LDF files)
EXEC sp_detach_db 'MyDatabase', 'true';

T-SQL command to detach a database, making its files available for moving or archiving.

Understanding the role of the MDF file is fundamental for any database administrator or developer working with SQL Server. It's the cornerstone of your database, holding the structure and data that your applications rely on.