Open MDF file data in SQL Server by attaching the file to an instance first. Four methods exist and they are SSMS and a script and Visual Studio and viewer software. Three of them need a healthy file from the same or an older SQL Server version. Viewer software previews a damaged or orphan file with no server and its free Quick Scan shows 500 records per table. Test every method on a copy.
SQL Server must attach an MDF file to an instance before any table appears. File Explorer cannot show the data inside.
Learn four ways to open an MDF file. Methods go from least effort to most, followed by the attach checks at the end.
Microsoft describes the primary file as the place for the startup information of a database. Four methods below open that file in different ways.
What is an MDF File in SQL Server?
Every SQL Server database has exactly one primary file and Microsoft recommends .mdf as its extension. Per Microsoft this file stores the startup information and points to the other files in database files and filegroups.
| File | Extension | Purpose |
|---|---|---|
| Primary data file | .mdf | Startup information and pointers to the other files |
| Secondary data file | .ndf | Optional user defined data files |
| Transaction log | .ldf | Information that SQL Server needs to recover the database |
Microsoft calls .mdf, .ndf and .ldf the recommended extensions and not required ones. Administrators can name a primary file differently.
Where is the Default Folder for Database Files?
SQL Server saves new database files in the DATA folder of an instance. Default instances follow the pattern below with the folder number from the table.
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\HotelDB.mdf
| SQL Server Version | Folder Number |
|---|---|
| 2022 | MSSQL16 |
| 2019 | MSSQL15 |
| 2017 | MSSQL14 |
| 2016 | MSSQL13 |
| 2014 | MSSQL12 |
| 2012 | MSSQL11 |
Named instances use their own folder names and administrators can move files elsewhere. Execute the statement below on a live instance to see the exact paths.
SELECT database_id, name, physical_name
FROM sys.master_files
WHERE type = 0
ORDER BY database_id;
What to Check Before You Open an MDF File?
Confirm six things before you attach the MDF file:
- Version. Per Microsoft the destination instance needs the same or a higher SQL Server version than the source.
- Log File. Microsoft expects every data file and log file to be available. Missing logs need a special step.
- Permission. Microsoft names three rights for an attach and they are CREATE DATABASE, CREATE ANY DATABASE and ALTER ANY DATABASE.
- File Access. The service account of the Database Engine needs access to the folder that stores the file.
- Copy First. Attach upgrades the database to the version of the instance and a copy protects the original.
- Trusted Source. Microsoft recommends that you never attach files from unknown sources and that you check them with DBCC CHECKDB on a nonproduction server.
Microsoft states that attaching a database that was never detached returns an error. Detach it on the source server first.
4 Methods to Open an MDF File
Four methods open the MDF file and three of them cost nothing. SSMS and T-SQL suit existing SQL Server instances. Visual Studio suits developers while viewer software suits damaged files and machines without SQL Server.
| Feature | SSMS | T-SQL | Visual Studio | Viewer Software |
|---|---|---|---|---|
| Cost | None | None | None | Free Quick Scan and paid Pro edition |
| Needs | A SQL Server instance | A SQL Server instance | Visual Studio with LocalDB or SQL Server | Windows PC and a copy of the file |
| Best for | Healthy files | Scripts and missing logs | Application developers | Damaged and orphan files |
| Biggest risk | Version mismatch | Wrong file path | Upgrade of the file | Quick Scan record limit |
Method #1: Attach the File in SSMS (Free, Needs SQL Server)
SQL Server Management Studio cannot display the data of an MDF file directly. Microsoft documents the Attach dialog in attaching a database in SQL Server and it adds the file to an instance so the database shows in Object Explorer.
- Connect to the Database Engine instance in SSMS.
- Right click Databases and select Attach.
- Click Add in the Attach Databases dialog and select the primary file.
- Check the file grid that fills in the other files of the database.
- Click OK and expand the new database to see its tables.
Note. Microsoft shows a Not found message beside a missing file. Correct the path of a missing log or remove the log entry from the grid. Error 5120 points to a permission problem and SQL Server Error 5120 on Database Attach explains the fix.
Method #2: Attach the Database With T-SQL (Free, Handles Missing Logs)
Scripts attach an MDF file with the same result as the dialog and you can reuse them on other servers. Microsoft documents both options in CREATE DATABASE for SQL Server. Replace the paths and the database name with your own.
CREATE DATABASE HotelDB
ON (FILENAME = N'D:\SQLData\HotelDB.mdf'),
(FILENAME = N'D:\SQLData\HotelDB_log.ldf')
FOR ATTACH;
Missing logs have a script option too. FOR ATTACH_REBUILD_LOG creates a fresh 1 MB log file when the original is gone.
Microsoft names two conditions for it and they are a clean shutdown and a database that accepts updates. Microsoft recommends a full database backup right after the operation since the log backup chain breaks.
CREATE DATABASE HotelDB
ON (FILENAME = N'D:\SQLData\HotelDB.mdf')
FOR ATTACH_REBUILD_LOG;
Confirm the result with the statement below.
SELECT name, state_desc, create_date
FROM sys.databases
WHERE name = N'HotelDB';
Method #3: Open the MDF File in Visual Studio (Free, for Developers)
Visual Studio connects to a database file through Server Explorer. Microsoft documents the steps in adding new connections in Visual Studio and names the instance (localdb)\MSSqlLocalDB. Visual Studio Installer adds that instance through the data storage and processing workload.
- Click the Connect to database icon or right click Data Connections and select Add Connection.
- Choose Microsoft SQL Server Database File (SqlClient) as the data source.
- Click Browse and select the file or type its path in the Database filename box.
- Select an authentication method and click Test Connection.
- Click OK and expand the new node to see the tables.
Microsoft documents these steps for Visual Studio 2019 so check the labels in your version.
Method #4: Open an MDF File Without SQL Server Using EmailDoctor (Quick Scan Free, Pro Paid)
Viewer software helps you open an MDF file that refuses to attach and works on computers with no SQL Server. EmailDoctor SQL Database Viewer scans the MDF file and shows all object types of the database in a tree.
EmailDoctor software needs Windows, .NET Framework 4.5 or newer and a launch with Run as Administrator. Free users get Quick Scan and the Pro edition adds Advance Scan and export to SQL Server. Use a copy of the MDF file.
Scan the MDF File in the Viewer
- Launch the viewer on your Windows system.
- Click the Open icon and browse to the MDF file.
- Select the Quick Scan option in the Add File dialog. Choose Advance Scan only after the Pro upgrade.
- Tick Auto detect SQL server file (.mdf) version or select the version yourself. Tick Preview Deleted Records to see removed rows.
- Click OK and wait for the scan to reach 100 percent. Press Close afterward.
- Press OK on the scan completed message. Advance Scan is the next step when Quick Scan shows fewer results than you expect.
- Expand Tables in the left tree and select a name to see its rows.
Quick Scan in the free edition shows only 500 records from a table. Press Save after the scan and the software stores it in STR format so a second scan becomes unnecessary.
What if You Cannot Open the MDF File?
Six problems explain most failed attempts:
- Version Is Newer. The file comes from a newer SQL Server version. Attach it on a server with a higher or equal version.
- Access Is Denied. The service account has no access to the folder. Microsoft lists this as a requirement.
- Log File Is Missing. Remove the log entry in the dialog or attach with a rebuilt log when the shutdown was clean.
- Database Is Already Attached. Attach returns an error for a database that is already attached according to Microsoft.
- MDF File Is Damaged. The attach fails or the database opens as suspect. Use method #4 or follow the steps in Repair a Corrupt SQL MDF File.
- Path Restriction. Microsoft documents a failed attach when the MDF and LDF sit in different folders and one path includes a GlobalRoot device path.
How to Protect a Database File Before You Open It?
Six habits prevent attach trouble:
- Copy First. Open a duplicate and store the original in a safe folder.
- Detach Before Moving. Detach the database on the source server before you copy its files.
- Move All Files. Take every data and log file of the database along when you copy it.
- Version Notes. Store the SQL Server version beside every MDF file you send.
- Real Backups. Microsoft warns that detach and attach is no backup method because detached files have no point in time recovery.
- Test Attach. Attach the file on a test server and check it with DBCC CHECKDB.
Frequently Asked Questions
Can I Open the MDF File Without SQL Server?
Yes. Viewer software scans the file and shows its objects with no SQL Server installed. EmailDoctor software handles healthy and damaged files.
Does SSMS Open the MDF File Directly?
No. SSMS attaches the file to an instance and the database appears in Object Explorer. Microsoft states that the Attach dialog fills in the database details after you add the primary file.
Do MDF, NDF and LDF Files Differ in Purpose?
Yes. MDF is the primary file and NDF is an optional secondary data file while LDF is the transaction log. Microsoft calls the extensions recommendations only.
Can I Attach the Database Without Its Log File?
Yes in many cases. Microsoft names two options for a missing log. FOR ATTACH recreates a single missing log after a clean shutdown and FOR ATTACH_REBUILD_LOG builds a replacement log for writable databases.
Can I Attach a File From a Newer SQL Server Version?
No. Microsoft rules out attaching a database to an earlier version. Choose a server with a matching or higher version.
Is It Safe to Attach an MDF File From an Untrusted Source?
No. Microsoft advises against attaching databases from sources you do not trust. Test the file on a nonproduction server with DBCC CHECKDB and review the code inside.
Does Attach Upgrade the File?
Yes. Microsoft states that attach upgrades the database to the internal version of the new instance. Open a duplicate when you need the original untouched.
Can Viewer Software Open a Corrupt MDF File?
Yes. EmailDoctor software scans corrupt and suspect MDF files and Advance Scan suits heavy damage. Attach in SSMS can fail on a damaged file.
Is the Free Viewer Limited?
Yes. Quick Scan in the free edition shows only 500 records from a table according to its on screen note. Pro edition adds Advance Scan and export.
Does the Size of the MDF File Restrict the Scan?
No. File size does not restrict the scan.
Can I Export Data From the Viewer?
Yes with the Pro edition. EmailDoctor software exports to a SQL Server database, SQL scripts and CSV files.
Author’s Verdict
Attach and viewer software are the two ways to open an MDF file and view its data. Check the version and the log file first and open a duplicate.
Use SSMS for a quick attach and T-SQL for scripts or a missing log. Visual Studio fits developers who already use Server Explorer.
Viewer software opens damaged and orphan files without a server. Backups with checksums and a test attach catch bad files early.
- MDF is the primary file of a SQL Server database.
- SSMS, T-SQL and Visual Studio attach it to an instance and need a matching version.
- Missing logs have a script fix in FOR ATTACH_REBUILD_LOG.
- Viewer software previews a damaged file with no server.
- Copy the file before you open it.