SQL Server uses a Master Database File (MDF) to store database information and objects like procedures, tables, functions, views, triggers, etc. Sometimes, when trying to connect to a database or attach an MDF file, you might have trouble finding the file.

In the upcoming section, we’ll discuss different ways to locate MDF files. You may also use a third-party application to access or find MDF location in SQL Server.

MDF File Location in SQL Server

Following are the two ways to determine location of an MDF file:

  1. Check the Default Location of MDF File

The default location of the MDF file depends on the SQL Server version (2019, 2017, 2016, and earlier) you’re using. All versions of SQL are located in the same directory named “Microsoft SQL Server.”

SQL Server 2014

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xxx.mdf

SQL Server 2016

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\xxx.mdf

SQL Server 2017

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\xxx.mdf

SQL Server 2019

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\xxx.mdf

Depending on your suitable version of SQL Server, you can easily locate your MDF file and navigate to it. All files that are common to be used by the servers or systems can be installed inside a folder named “nnn.” The exact file location is folder:\Program Files\Microsoft SQL Server\nnn\

  1. Use T-SQL Query

You can get the list of log and data files in the SQL server by running the following T-SQL query:

USE master;

SELECT

name ‘Logical Name of File,’

physical_name ‘File Location’

FROM sys.master_files;

If you need to find database files for a specific database, execute this T-SQL code:

USE database_name;

SELECT

name ‘Logical Name of File,’

physical_name ‘File Location’

FROM sys.master_files;

In this code, replace ‘database_name’ with the name of your db.

What Else Can I Do to Find MDF File?

If you still can’t find the MDF file, you can use third-party tools to find MDF location in SQL Server. You can search for a specialized MDF file viewer tool to find an MDF file and view its contents. However, you may come across situations when the MDF file you’ve found is corrupted and won’t open. In that case, using a SQL repair tool may help. It provides options to browse or search an MDF file and repair it with all its data intact.

Final Words

If you cannot find your SQL Server database MDF file, check out the ways discussed in this post to determine where the file is located. If that doesn’t help, using a third-party application can help you locate the MDF files. Once you have located MDF files, it is important to follow practices to keep the file and its data secure. Some of these are as follows:

  • Frequent File Backups

Many businesses have to take backup of their database files, especially when the business size grows. They have to save records and information regularly to handle technical issues like accidental file deletion, hardware or software corruption, etc., whenever they arise. When any MDF file of the database goes missing or becomes corrupt, the database backup can be used to restore the file.

  • SQL Backup File Verification

The backup file health needs to be verified regularly. The health of the backup files means the availability and readability of the files. When the backup file size increases, the database administrator needs to save the file properly.

  • Database Consistency

Sometimes database queries produce irrelevant output. This happens when the database becomes corrupted, or some information is stored in the wrong place. The database should be consistent, and all information should be made available at the right time.

  • Deadlock Removal

There can be multiple databases stored in a single SQL Server in which many users may run their database queries. The database server has to process multiple queries that may sometimes cause a deadlock situation. Such situations should be fixed and avoided.