Read time 6 minutes
Microsoft SQL Server stores two types of data files in default directories on respective systems, which are known as Master Database Files and Log Database Files and abbreviated as MDF and LDF data files. Both these files are primarily available in the SQL Server environment.
However, a database can also have the NDF data file, which is known as a non-primary or secondary data file. Thus, the precedence of the MDF file is slightly higher than that of NDF, but both play an important role in holding crucial database information.
It is possible to access/open the MDF file either on the same or different computer system using third-party standalone software, and this eliminates the need to have the SQL Server installed & configured on the system and, thus, saves time, extra effort, and money.
In this article, we’ll guide you through the default MDF file location in SQL Server 2014, 2016, and 2017, followed by shedding light on how to open and view MDF files without SQL server.
Default location of MDF file in SQL server
Files that are common and used by all instances on a single system are installed inside the folder
Here,
Note: For most program installations, C drive is the default location.
Use the information given above to figure out the current MDF file location on your system; you only need to know the major and minor version number of current SQL Server installed on your PC.
Below, find the default MDF file location in different versions of Microsoft SQL Server:
Default MDF file location for SQL Server 2014:
Default MDF file location for SQL Server 2016:
Default MDF file location for SQL Server 2017:
Default MDF file location for SQL Server 2019:
Navigate to the default MDF file location given above, according to the Microsoft SQL Server version, and obtain the MDF file for whatever the purpose you need it for.
Precautions to prevent your SQL Server files
If you are aware of the location of the MDF files, then you should also know the techniques that can help you secure your database.
- Take frequent backups
Many businesses take backups of the database files only when they grow in size or when it becomes necessary to take the backup. A regular backup can save the database from major issues like software-related corruption, hardware-related corruption, or accidental deletion. When a database gets corrupted, then the backup file can help in restoring the data. - Verify the SQL backups
Taking SQL backups will be only half the job, as you need to verify the health of the backups routinely. It means that you should check the backup files and their readability and availability. Sometimes, when the size of the backup files increases, the administrator needs to save the backups at a safe location. - Do not shrink database regularly
Many times, the administrator tries to shrink the database to save the storage space. But, there is a problem with it as it increases the fragmentation in the database. Due to the excessive database shrinking, the performance also will be affected. - Check database consistency
Sometimes, when the users run a query to fetch the data, then they produce irrelevant information. It is because the database has been misplaced or has become corrupt. In extreme conditions, the whole server may go down. - Remove deadlocks
A single SQL Server can have multiple databases where many users are running their queries. Sometimes, a large database can have multiple queries to process, and this can create a deadlock situation. So, the administrator should check for such a deadlock situation and fix them as soon as possible.
A simple and free alternative to open and view MDF files
Once you know the default MDF file location, the next step is to open and view its data (database objects and sub-data) stored inside, but the question is how you would open the MDF file without the SQL server. The answer is to download and use a standalone MDF viewer software.
Kernel for MDF Viewer is one such software, that assists the user in the process of opening and viewing the MDF file(s) with no additional software needed. It enables the user to load both healthy and corrupt or damaged MDF/NDF files instantly and offers a detailed view of data. Additionally, the tool enables the user to preview items deleted from the database, preserves data integrity, and maintains file structure & original file properties.
How to open and view MDF file data
Follow the steps given below to open and view MDF file:
- Open Kernel for MDF Viewer.
- Click on Browse button to open the MDF file saved at a specific and click Recover.
Note: If the MDF file is badly corrupt or damaged, then select Advanced Mode and proceed. If the SQL database version is not detected automatically, select the SQL Database version manually, as shown below:
- Upon successful file scan and data recovery, the tool’s UI will refresh and load all database objects in the left pane, as shown below. Click on the + icon to expand an object:
Note: Double-clicking on any object will also expand it.
- Now, click on any object to view its data.
Wrap
You can figure out the default MDF file location using the tips mentioned above. The only thing you should know is the SQL Server’s major & minor version. Occasionally, MDB database files get corrupted, and for that reason, users are not able to open and view their MDB files. For opening and viewing the MDF database file, Kernel for MDF Viewer is the best tool. It enables the users to repair damaged MDF & NDF files, fixes inaccessibility issues, and assists to fix data corruption in SQL server and MDF recovery.
