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;
MDF file
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 schema, data and 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.
LDF file
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.
Message – Could not find a table or object name ‘%.*ls’ in database ‘%.*ls’.
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.
Message – Database ‘%.*ls’ cannot be configured as a distribution database because it has change tracking enabled.
Message – Object ID %ld specified as a default for table ID %ld, column ID %d is missing or not of type default.
Message – The current user account was invoked with SETUSER or SP_SETAPPROLE. Changing databases is not allowed.
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;
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.
SELECT
name ‘Logical Name’,
physical_name ‘File Location’
FROM sys.master_files;
Another way to find the location of MDF and LDF files is to expand the databases section in the SQL Server Management Studio, right-click on the selective database and select Properties option from the drop-down list. Then, on the Database Properties page, select Files from the left panel and then check out the given path locations for both MDF and LDF files on the right panel.
Sometimes SQL Server database file is not accessible due to corruption errors as mentioned before in the blog or other systematic errors. So, it is always recommended to have regular backups of SQL Server database files with checks on the database consistency. For this issue, 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.
