What Is an MDF File? A Database Made of Pages, and What Happens When Pages Break

7 How Do You Open an MDF File? Attach it to SQL Server, use LocalDB or read it with a viewer

We read MDF files with our own scripts on Linux. We did not start SQL Server, SQL Server Management Studio or the two viewers. The steps below come from Microsoft’s pages and from the pages of the two vendors, and we say so where it matters.

Attach an MDF file in SQL Server Management Studio

Microsoft’s page on attaching a database names the steps. Before you start, know that the database must be detached from any instance first. All data files must be available, and you need the CREATE DATABASE, CREATE ANY DATABASE or ALTER ANY DATABASE permission.

1. 1 Copy the MDF file and the LDF file to a folder that the server can read.
2. 2 In SQL Server Management Studio, right click Databases and select Attach.
3. 3 In the Attach Databases dialog box, select Add and select the .mdf file in the Locate Database Files box.
4. 4 If the dialog lists a log file that is missing, correct its path in the grid or remove it, as Microsoft says.
5. 5 Select OK, and then check the tables. Remember that the MDF file is upgraded when it attaches.

Attach an MDF file with a Transact-SQL command

Microsoft’s page on CREATE DATABASE says FOR ATTACH creates a database by attaching existing operating system files. The page on sp_attach_db says that older procedure will be removed in a future version and recommends CREATE DATABASE FOR ATTACH instead.

CREATE DATABASE northwind
ON (FILENAME = N'C:\Data\northwind.mdf')
FOR ATTACH;

Add the path of the LDF file to the command when you have one. Without a log file, Microsoft says the attach operation tries to build a new one. That works for the read write database that was shut down properly, and the causes part lists the limits.

Open an MDF file with LocalDB

Microsoft’s page on LocalDB calls it a feature of SQL Server Express for developers. It gives the connection string that names the MDF file, with Server set to (LocalDB)\MSSQLLocalDB and AttachDbFileName set to the path of the .mdf file. We did not test it.

Where MDF files live

Microsoft says data files live in a Data folder, in the MSSQL folder of the instance, under the Microsoft SQL Server folder in Program Files. On Linux, the mssql-conf page gives /var/opt/mssql/data as the default folder for new database files.

Your administrator can change the default folder, so the database properties in Management Studio show the full path of a file. A file that you find by name is only a candidate, because the ending is a recommendation.

Open an MDF file with a viewer

Two vendors list viewers for MDF files on Windows. We tested neither, and both pages come from the vendors.

The SysTools page lists version 13.0 and 6.72 MB. It opens MDF and NDF files without a SQL Server instance, and it names SQL Server 2000 to 2022 and Windows 7 to 11. The free version scans and previews. The page lists the Pro edition at $249 that exports to a SQL Server database, scripts or CSV.

The Freeviewer page also lists version 13.0 and 6.72 MB, a free version and the Pro edition at $249. It names SQL Server 2000 to 2025. The two pages name different version ranges for the free tool, so read the page before you download.

Other ways to read an MDF file

FileInfo’s page on .mdf files lists Microsoft SQL Server 2019 and Visual Studio 2022 for the SQL Server entry. A Microsoft forum answer says an MDF file is binary and not meant to be opened in an application. It says to attach it to a running server and read it with Transact-SQL.

If your file is the disc image from Alcohol 120%, Wikipedia says Daemon Tools, CDemu, MagicISO, PowerDVD and WinCDEmu read the format. Look for a file with the same name and the ending .mds beside it.

How our tools did on the damaged MDF files

Every tool gets its own row below. The numbers cover our 132 damaged copies, and the tools that read files without a server are the ones we used.

8 How Do You Check an MDF File? Six steps and two short scripts

The six steps to check an MDF file

1. 1 Copy the MDF file, write down its SHA256 hash and work on the copy.
2. 2 Check the size with wc -c < mydata.mdf. It must be a multiple of 8,192 bytes, and a primary file has at least 10 pages.
3. 3 Read the first bytes with xxd -l 32 mydata.mdf. They should start with 01 0F 00 00. All 4 of our public MDF files do.
4. 4 Read the version with xxd -s 73824 -l 16 mydata.mdf. Counting from 0, bytes 4 and 5 of that line are the database version and bytes 6 and 7 the creation version, low byte first. Our sample shows 8f 02 and 1b 02, which are 655 and 539.
5. 5 Count the rows with the row counter below and compare the totals with your source.
6. 6 Use the 64 line script below. Stop if a check names a fault, if the version is too new for your server or if the MDF file came from a source you do not trust.

$ sha256sum northwind.mdf
bc4adbda51be2f1463c11387255e7a5a37db20e45980ce5554812c37e7f52876  northwind.mdf
$ file northwind.mdf
northwind.mdf: data
$ wc -c < northwind.mdf
3538944

$ xxd -l 32 northwind.mdf
00000000: 010f 0000 0801 0000 0000 0000 0000 0000  ................
00000010: 0000 0000 0000 0100 6300 0000 6a1b 9404  ........c...j...
$ xxd -s 73824 -l 16 northwind.mdf
00012060: 0000 a405 8f02 1b02 0000 0000 0000 0000  ................

The file command says data, because it has no rule for an MDF file. The hash is the line that matters. It shows which file you have, and it must match the source before you go on.

The row counter for MDF files

The Python script below does step 5. It reads the pages that the PFS page marks as in use. On every data page at level 0 it takes the number of row slots, subtracts the ghost rows and adds the result to the table number of the page. It prints the biggest tables and the total.

#!/usr/bin/env python3
"""mdf_rows.py counts the rows of every table from the data pages of an MDF file.
Use: python3 mdf_rows.py mydata.mdf. It changes no file and starts nothing."""
import struct, sys, collections
PAGE = 8192

def rows_by_object(data):
    n = len(data) // PAGE
    out = collections.Counter()
    for start in [1] + list(range(8088, n, 8088)):
        pfs = data[start * PAGE:(start + 1) * PAGE]
        for k in range(8088):
            i = k if start == 1 else start + k
            if i >= n or not pfs[100 + k] & 0x40:
                continue
            p = data[i * PAGE:(i + 1) * PAGE]
            if p[1] == 1 and p[3] == 0:
                slots, obj, ghost = struct.unpack_from('<H', p, 22)[0], struct.unpack_from('<I', p, 24)[0], struct.unpack_from('<H', p, 58)[0]
                out[obj] += slots - ghost
    return out

if __name__ == '__main__':
    rows = rows_by_object(open(sys.argv[1], 'rb').read())
    for obj, count in rows.most_common(int(sys.argv[2]) if len(sys.argv) > 2 else 6):
        print('object %d: %d rows' % (obj, count))
    print('%d objects, %d rows in all' % (len(rows), sum(rows.values())))

It prints table numbers and not names, because the names live in system tables that it does not read. On our sample it prints 7,201 rows in 38 objects. Object 21575115 is the Orders table with 830 rows. The script changes no file and starts nothing.

$ python3 mdf_rows.py northwind.mdf 4
object 325576198: 2155 rows
object 21575115: 830 rows
object 3: 792 rows
object 41: 705 rows
38 objects, 7201 rows in all

Compare the two lists, yours and the source’s, and the totals. A copy with one data page zeroed prints 6,940 rows, and one copy cut at 10 percent prints 781. The counter found none of the 21 silent copies, because one changed byte does not change a row count.

The 64 line script that reads MDF files

The Python script below does step 6. It checks the size, the start of page 0 and the type and version of the boot page. Then it reads the PFS page. For every page that the PFS page marks as in use it checks the page number, the file number and the checksum or the torn page bits.

It names every fault it finds, and it changes no file. It computes the checksum the way SQL Server does, with an exclusive or over the words of every 512 byte sector. It does not read pages that the PFS page marks as free, because they can contain old content.

#!/usr/bin/env python3
"""mdf_peek.py reads the structure of an MDF file and names every fault it can see.
Use: python3 mdf_peek.py mydata.mdf. It changes no file and starts nothing."""
import struct, sys
PAGE = 8192
VERSIONS = {539: '2000', 611: '2005', 612: '2005', 655: '2008', 661: '2008 R2', 706: '2012',
            782: '2014', 852: '2016', 869: '2017', 904: '2019', 957: '2022'}

def rotl(x, n):
    return ((x << n) | (x >> (32 - n))) & 0xFFFFFFFF if n else x

def checksum(page):
    w = list(struct.unpack('<2048I', page))
    w[15] = 0
    out = 0
    for i in range(16):
        s = 0
        for v in w[i * 128:(i + 1) * 128]:
            s ^= v
        out ^= rotl(s, 15 - i)
    return out

def peek(data):
    msgs, n = [], len(data) // PAGE
    if len(data) % PAGE:
        msgs.append('size %d is not a multiple of %d' % (len(data), PAGE))
    if n < 10:
        return msgs + ['only %d pages, a primary file has at least 10' % n]
    page = lambda i: data[i * PAGE:(i + 1) * PAGE]
    if page(0)[:4] != b'\x01\x0f\x00\x00':
        msgs.append('page 0 does not start with 01 0F 00 00 (file header page)')
    if page(9)[1] != 13:
        msgs.append('page 9 has type %d, the boot page has type 13' % page(9)[1])
    elif struct.unpack_from('<H', page(9), 100)[0] not in VERSIONS:
        msgs.append('database version %d is not one we know' % struct.unpack_from('<H', page(9), 100)[0])
    for start in [1] + list(range(8088, n, 8088)):
        pfs = page(start)
        if pfs[1] != 11:
            msgs.append('page %d should be a PFS page and has type %d' % (start, pfs[1]))
            continue
        for k in range(8088):
            i = k if start == 1 else start + k
            if not pfs[100 + k] & 0x40:
                continue
            p = page(i) if i < n else b''
            if not any(p):
                msgs.append('page %d is in use by the PFS page but %s' % (i, 'is missing' if i >= n else 'is empty'))
                continue
            pid, fid = struct.unpack_from('<IH', p, 32)
            if pid != i or fid != 1:
                msgs.append('page %d says it is page %d of file %d' % (i, pid, fid))
            flag, torn = struct.unpack_from('<H', p, 4)[0], struct.unpack_from('<I', p, 60)[0]
            if flag & 0x200 and checksum(p) != torn:
                msgs.append('page %d fails its checksum' % i)
            elif not flag & 0x200 and flag & 0x100 and any((p[s * 512 + 511] & 3) != torn & 3 for s in range(1, 16)):
                msgs.append('page %d fails its torn page bits' % i)
    return msgs

if __name__ == '__main__':
    data = open(sys.argv[1], 'rb').read()
    out = peek(data)
    print('\n'.join(out[:20]) if out else 'no problem found in %d pages' % (len(data) // PAGE))
    if len(out) > 20:
        print('... and %d more' % (len(out) - 20))

On our sample it reads 432 pages and reports no problem. It read AdventureWorks, with 26,272 pages, in 2.4 seconds. It flagged none of the 4 intact MDF files. It names a fault only when the pages disagree with their own header.

$ python3 mdf_peek.py northwind.mdf
no problem found in 432 pages

$ python3 mdf_peek.py k12.mdf
page 195 is in use by the PFS page but is empty

$ python3 mdf_peek.py k14.mdf
page 408 says it is page 4123831670 of file 43764
page 408 fails its checksum

The copy with a junk page shows two faults at once. The page says it is page 4123831670 of file 43764, and it also fails its protection value. The script names the page, and you copy the MDF file again from its source.

What the checks cannot see

38 copies passed our script. 21 had one changed byte in a data page, and 17 had a change in an index page, an unused page or the end of the MDF file. The three torn bit files hide one changed byte, and junk added at the end is never read.

Write down the facts of your original MDF file. Keep its size, its SHA256 hash, its two version numbers and the row total. A copy that differs in any of them is a different file, even when every tool reads it.

Our scripts never check that a name, a number or a date is right. They show whether the pages agree with themselves, and nothing more. We did not test an encrypted file, so we cannot say how the script reads one.

Want the causes of an error? Go to the causes.

9 Is an MDF File Safe to Open and Share? Reading starts no code, and attaching asks for trust

Microsoft’s page on sp_attach_db warns that such a database could contain malicious code. The code could execute unintended Transact-SQL or damage the schema and the physical structure. The page advises DBCC CHECKDB on a nonproduction server, plus a look at stored procedures and other code in the database.

We scanned no file for malware, and our 4 MDF files are public samples. Microsoft’s page on DBCC CHECKDB describes the check. Our scripts only read bytes, so reading with them starts nothing.

What is inside an MDF file

$ strings -e l northwind.mdf | head -3
Northwind
Northwind
Northwind

The log file next to an MDF file

An attach needs the data files, and Microsoft says to keep all the detached log files until the database attaches without them. The log records the changes in order, and our page on LDF files shows that it can contain old values. Look at both files before you send either.

Before you share an MDF file

1. 1 Attach your copy on any test server and list the tables. Look at what is there.
2. 2 Ask if the other side needs every table. Send fewer tables when it needs fewer.
3. 3 Leave the LDF file out unless the other side needs it, and read what it contains first.
4. 4 Send the MDF file over a channel you trust, and send its SHA256 hash by another route.
5. 5 Delete your extra copies when the other side confirms the MDF file.

11 How Do You Move or Convert an MDF File? Detach and attach, restore a backup or count the rows without a server

This part reports what Microsoft says and what we tested with a rename. A converter would read the MDF file and write new files, and the original file stays unchanged.

Move an MDF file to another server

Microsoft’s page on upgrading with detach and attach says the database is available immediately after the attach and is automatically upgraded. It says this prevents the database from being used with an older version. A move to any newer server cannot be undone, so keep a copy.

Size limits that can block a move

Microsoft’s page for SQL Server 2022 lists 10 GB as the maximum relational database size of the Express edition. The same row on the page for SQL Server 2019 says 10 GB. Microsoft’s list of what is new in SQL Server 2025 says 50 GB. Our biggest file is 215,220,224 bytes.

Restore a backup instead

A backup is a different file. Microsoft’s page on full backups says to use .bak as the extension to simplify file classification. The page for DBCC CHECKDB recommends a restore from the last known good backup. Our page on MDF repair lists when to prefer it.

Read the rows without a server

Our row counter totals the rows of every table from the data pages, and it prints no values. The pages of SysTools MDF Viewer and Freeviewer MDF Viewer list the Pro edition at $249 that exports. SysTools names SQL Server, scripts and CSV. We tested neither.

What you cannot convert by renaming an MDF file

Renaming an MDF file to .ndf changes only the name. We renamed our sample, and the SHA256 hash stayed the same and the file command still said data. We read no NDF file, so we cannot say how its first page differs from page 0 of an MDF file.

$ sha256sum northwind.mdf northwind-renamed.ndf
bc4adbda51be2f1463c11387255e7a5a37db20e45980ce5554812c37e7f52876  northwind.mdf
bc4adbda51be2f1463c11387255e7a5a37db20e45980ce5554812c37e7f52876  northwind-renamed.ndf
$ file northwind-renamed.ndf
northwind-renamed.ndf: data