What is an MDF File? SQL Server Database Explained

📋 On This Page

What is MDF? Quick Facts What MDF Stores MDF vs NDF vs LDF File Structure Versions & Compatibility Size Limits How to Open Default Location Common Uses Troubleshooting FAQ

📖 What is an MDF File?

An MDF file is the primary data file of a Microsoft SQL Server database. It is the main file where SQL Server keeps the real contents of a database, and it is often read as the Master Database File or Main Database File.

Every SQL Server database has at least one MDF file. It stores your tables and their rows, plus indexes, views, stored procedures and the startup information that points to the other files the database uses. The file is binary and page-structured, so you cannot read it in a text editor. To open it you attach it in SQL Server or use a dedicated MDF viewer.

Database Objects

Tables, rows and columns
Indexes and keys
Views and stored procedures
Functions and triggers

System Information

File header and boot page
Allocation maps for free space
Pointers to secondary and log files
Internal database version number

💡 One extension, three formats: The .mdf extension is also used by disc tools like Alcohol 120% and DAEMON Tools for a Media Descriptor File that stores a CD or DVD image, usually paired with an .mds file. It is used as well by the automotive industry for a Measurement Data File that holds sensor readings. This guide covers the Microsoft SQL Server database file, which is the meaning most database users need.

⚡ Quick Facts

File Extension	`.mdf`
Full Name	Primary Data File (commonly called Master Database File)
Category	Database File
Developed By	Microsoft Corporation
Used By	Microsoft SQL Server
File Type	Binary (proprietary, page-based)
Page Size	8 KB (8,192 bytes)
Extent Size	64 KB (8 pages)
Companion Files	`.ndf` (secondary data), `.ldf` (transaction log)
Opens With	SQL Server (SSMS); MDF viewers for read-only access
Max Data File Size	16 TB per file (10 GB per database on Express)

📦 What an MDF File Stores

An MDF file is not a plain container of records. It stores everything SQL Server needs to run the database, organised into fixed-size pages. Here is what lives inside.

📊

User data
All table rows and column values

🔑

Indexes and keys
Structures that speed up queries

⚙️

Programmable objects
Stored procedures, functions, triggers

👁️

Views and schemas
Saved queries and object grouping

🗺️

Allocation maps
Track which pages are used or free

🧾

Metadata
File header and database version

⚖️ MDF vs NDF vs LDF

A SQL Server database is made of up to three kinds of files. Knowing the difference helps you move, back up and open a database correctly.

The .mdf holds the data, optional .ndf files add space and the .ldf keeps the log. Move them as one set.

	`.mdf`	`.ndf`	`.ldf`
Name	Primary data file	Secondary data file	Transaction log file
Required	Yes, exactly one	No, optional	Yes, at least one
Holds	Data plus startup info	Extra data only	A record of every change
Uses 8 KB pages	Yes	Yes	No, uses log records
Purpose	Main storage	Spread data across files or drives	Recovery and consistency

⚠️ Keep the files together: SQL Server does not enforce the .mdf, .ndf and .ldf names, but it does need the matching files to open a database. When you copy or move a database, move the .mdf, every .ndf and the .ldf as a set.

🧩 MDF File Structure

SQL Server stores everything in an MDF file on pages. A page is the smallest unit the engine reads or writes. Understanding pages explains how the file is laid out.

8 KB

Page

The basic storage unit, numbered from 0 to n

Byte header

Page ID, type, record count and page pointers

64 KB

Extent

A group of 8 physically neighbouring pages

Each 8 KB page begins with a 96-byte header, which leaves about 8,060 bytes for row data. Pages are grouped into extents of 8 pages. The first pages of the file hold system information such as the file header and allocation maps, then the data and index pages follow.

# Inside a SQL Server primary data file
MyDatabase.mdf  (primary data file)
Page 0   File header page      # metadata about the file
Page 1   PFS                   # Page Free Space
Page 2   GAM                   # Global Allocation Map
Page 3   SGAM                  # Shared Global Allocation Map
Page 9   Database boot page     # primary file only
Page n   Data and index pages   # your tables and indexes

# Each page is 8 KB (8,192 bytes)
#   96-byte header + about 8,060 bytes for rows
# An extent = 8 pages = 64 KB

SQL Server reads and writes whole 8 KB pages, and groups them into 64 KB extents.

Special system pages keep the file organised. The file header page (page 0) describes the file itself. Allocation maps such as PFS, GAM and SGAM track which pages and extents are free or in use. The transaction log (.ldf) works differently and does not use pages at all, it stores a stream of log records instead.

📊 Versions & Compatibility

Every MDF file stores an internal database version number that ties it to the SQL Server release that created or last upgraded it. This drives one important rule.

🚫 Upgrades only go forward: You can attach or restore an MDF file to the same SQL Server version or a newer one, but never to an older version. To move data to an older server you must script out the tables and data on the newer server, then import them.

To move data to an older server, script out the objects and data instead of attaching the file.

The modern 8 KB page format has been used since SQL Server 7.0 (1998). Earlier versions used 2 KB pages. Each release since then keeps the page format but raises the internal version and adds features. Databases also carry a compatibility level that controls query behaviour.

SQL Server Release	Year	Compatibility Level
SQL Server 2008 / 2008 R2	2008 / 2010	100
SQL Server 2012	2012	110
SQL Server 2014	2014	120
SQL Server 2016	2016	130
SQL Server 2017	2017	140
SQL Server 2019	2019	150
SQL Server 2022	2022	160

📏 Size Limits

Maximum data file size

16 TB

A single .mdf or .ndf data file can grow up to 16 terabytes.

SQL Server Express limit

10 GB

The free Express edition caps each database at 10 GB.

✅ Performance tip: Keep the .mdf and the .ldf on separate drives when you can, and set a sensible autogrowth value so the files do not grow in tiny steps. Avoid autoshrink, since it fragments indexes.

📂 How to Open an MDF File

There are two paths. Use SQL Server when you want to run the database, or an MDF viewer when you only need to read the data.

Attach in SQL Server Management Studio

Open SQL Server Management Studio and connect to your instance.
Right-click the Databases node and choose Attach.
Click Add, then select your .mdf file. Check that the matching .ldf is listed.
Click OK. The database appears under Databases, ready to query.

Attach with T-SQL

-- Attach a database from its MDF and LDF files
CREATE DATABASE MyDatabase
    ON (FILENAME = 'C:\Data\MyDatabase.mdf')
    LOG ON (FILENAME = 'C:\Data\MyDatabase.ldf')
    FOR ATTACH;

Open Without SQL Server

If you do not have SQL Server, or the file is corrupt or orphaned, a dedicated tool can read it directly:

Use the Univik MDF Viewer to open and read tables and objects without a server, or the Univik MDF Converter to export the data to SQL scripts or CSV for use in other systems.

⚠️ Do not open an MDF file in Notepad or Excel: The file is binary, so a text editor only shows unreadable characters and can corrupt the file if you save it. Use SQL Server or a proper MDF viewer instead.

📍 Default MDF File Location

By default, SQL Server saves each database's .mdf and .ldf files in the DATA folder of its instance. The folder name carries an internal version number, so it changes with each SQL Server release. For a default instance the base path is C:\Program Files\Microsoft SQL Server\.

SQL Server Release	Default DATA Folder (default instance)
SQL Server 2008	`...\MSSQL10.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2008 R2	`...\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2012	`...\MSSQL11.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2014	`...\MSSQL12.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2016	`...\MSSQL13.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2017	`...\MSSQL14.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2019	`...\MSSQL15.MSSQLSERVER\MSSQL\DATA\`
SQL Server 2022	`...\MSSQL16.MSSQLSERVER\MSSQL\DATA\`

💡 Find the exact path: A named instance replaces MSSQLSERVER with the instance name, such as MSSQL15.SALES. To see the real location of every file, run SELECT name, physical_name FROM sys.master_files; in SQL Server.

🎯 Common Uses

🗄️ Application databases

Websites, ERP, CRM and line-of-business apps store their data in MDF files behind SQL Server.

💾 Backup and migration

Teams detach or back up databases and move the .mdf set to another server or environment.

🔁 Attach and detach

Detaching a database frees its files so they can be copied, then re-attached elsewhere.

🔍 Recovery and forensics

Analysts read orphaned or corrupt MDF files to recover records when a server is unavailable.

🔧 Troubleshooting

You are attaching a file created on a newer SQL Server. Attach it on the same or a newer version. To use it on an older server, script out the objects and data on the newer server and import them there.

Run DBCC CHECKDB to check for consistency errors and restore from a good backup when possible. To repair the file and recover the data without a server, use SQL Database Recovery. To just read what is still accessible, an MDF viewer can open many damaged files.

If the database was shut down cleanly, SQL Server can rebuild the log with CREATE DATABASE ... FOR ATTACH_REBUILD_LOG. If it was not, you may need emergency mode with repair or a dedicated SQL Database Recovery tool that reads the MDF without the log.

Error 5120 means the SQL Server service account cannot reach the files. Give that account, or its service SID, read and write permission on the .mdf and .ldf files. Running SSMS as administrator on its own does not fix this. Also make sure the database is detached or offline before you copy the files, since SQL Server locks files that are in use.

❓ Frequently Asked Questions

An MDF file is the primary data file of a Microsoft SQL Server database. It holds the real data of the database, including tables, rows, columns, indexes, views, stored procedures and the pointers to any other files the database uses. The .mdf extension is the recommended name for this primary data file.

MDF is the primary data file and every database has exactly one. NDF is an optional secondary data file used to spread a database across more files or drives. LDF is the transaction log file that records every change so the database can be recovered. SQL Server does not force these extensions, but they are the standard convention.

The normal way is to attach it in Microsoft SQL Server. In SQL Server Management Studio, right-click Databases, choose Attach and add the .mdf file. You can also run CREATE DATABASE ... FOR ATTACH in T-SQL. If you do not have SQL Server, a dedicated MDF viewer can open and read the file without a server.

Yes. A third-party MDF viewer can read the tables and objects inside an .mdf file without a running SQL Server instance, which is useful for a quick look or for corrupt and orphaned files. To attach the database and run queries you still need SQL Server, or you can convert the data to SQL scripts or CSV first.

No. Each MDF file stores an internal database version number. A database can be attached or restored to the same version or a newer version of SQL Server, but never to an older one. To move data to an older server you must script out the objects and data on the newer server and import them, rather than attaching the file.

Usually yes. The transaction log (.ldf) is part of the database and SQL Server expects it when you attach. If the log is missing, SQL Server can sometimes rebuild it using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, but this only works when the database was shut down cleanly. Keep the matching .ldf with the .mdf whenever you move a database.

A single SQL Server data file can grow up to 16 TB, so an .mdf file can be very large. The SQL Server Express edition is different: it limits each database to 10 GB. For performance, many teams keep the .mdf and .ldf on separate drives and set sensible autogrowth values.

No. The .mdf extension is used by two unrelated formats. This guide covers the Microsoft SQL Server primary database file. The other .mdf is a Media Descriptor File created by disc tools like Alcohol 120% and DAEMON Tools to store a CD or DVD image, usually paired with an .mds file. They share an extension but are completely different files.