SQL Server from Microsoft has proved itself as a reliable backbone of an application, whether small or large. It is the most well-known relational database system which quickly processes the queries from the user and brings the necessary data to you. The primary job of the SQL Server is to store the data in the database and retrieve them quickly. Majorly, SQL Server stores the database in two different file extensions, MDF and LDF files. You do not need to create them manually because these files are created automatically as soon as you create a database in the SQL Server.
SQL Server Database
As we mentioned about two data files which store the SQL Server data, let’s check them in detail;
It is the major file format that stores the database which you create in the Server, and the queries which you write to store or fetch the data are targeted to the MDF file. It stores all the database objects like tables, procedures, views, functions, triggers, etc. If there is any corruption in the database, then you will not be able to access your database.
The LDF file stores the various transaction log files and all the information of all the modifications that are applied to the database.
Although you do not need to know the location of the MDF file as you can interact with the files with your queries, there are some unwanted situations like database corruption when you need to get the MDF file location to recover & repair MDF file.
Here is a small list of various error messages that you may see if there is corruption in SQL Server.
Error – 2506
Message – Could not find a table or object name ‘%.*ls’ in database ‘%.*ls’.
Error – 2520
Message – Could not find database ‘%.*ls’. The database either does not exist or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.
Error – 2712
Message – Database ‘%.*ls’ cannot be configured as a distribution database because it has change tracking enabled.
Error – 223
Message – Object ID %ld specified as a default for table ID %ld, column ID %d is missing or not of type default.
Error – 505
Message – The current user account was invoked with SETUSER or SP_SETAPPROLE. Changing databases is not allowed.
Error – 672
Message – Failed to queue cleanup packets for orphaned rowsets in database “%.*ls”. Some disk space may be wasted. Cleanup will be attempted again on database restart.
If you are facing any such error, then you can remove it manually or by using any professional tool, but in both conditions, you need to have the information of the MDF File location.
Location of MDF file
There are multiple versions of SQL Server 2019, 2017, 2016, 2014, and the previous ones. But the common path remains as Program Files\Microsoft SQL Server\\\.
So, the default MDF file location will be the following in different SQL Server versions;
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
You can easily navigate your way towards the MDF file location as per the version of the SQL Server you are using and obtain the file.
SQL administrators can also run this T-SQL command in the Microsoft SQL Server Management Studio (MSSMS) to get the list of the complete data files and log files of the SQL Server.
name ‘Logical Name’,
physical_name ‘File Location’
Sometimes SQL Server database file is not accessible due to the corruptions errors as mentioned before in the blog or other systematic errors. Check out the best SQL Database recovery tool that thoroughly scans the SQL Server database, perform repair on it and recover the whole database objects to save it to the server again. It supports all SQL Server versions and is the most secure software. Visit its official website to know more.