How to find database file location in SQL Server

It is pretty much straightforward to find database file location in SQL Server. When a database is created in SQL Server, two files are automatically created, a data file and a log file. SQL Server database stores data in these two types of files, a data file (MDF) and a transaction log file (LDF). These data files are part of a filegroup.

• MDF files are the main data files that hold different database objects like tables, stored procedures, views, and indexes.
• LDF files are SQL Server transaction log files that are used to record all the transactions and different DML operations.

There is a default database file location for instances depending on the Microsoft SQL Server version. Below are examples of default SQL database file locations for SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022.

• SQL Server 2016 — C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2017 —C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2019 —C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2022 —C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\

Find SQL database file location using SSMS

You can use the Microsoft SQL Server Management Studio (SSMS) to locate the SQL server database files.

Open the SQL Server Studio Management, right-click on the database, and then select Properties.

A new window will appear, select the files page and you will see the data file and log files location.

Find SQL database file location using TSQL

Another way to find the database file location is to run a TSQL query. Right-click on the SQL Instance and click on New Query, and run the below-mentioned query.

SELECT name, physical_name AS file_location
FROM sys.master_files

Conclusion:

In this article, we have shown you two methods to locate SQL Server database data files and Log file locations. One way is to simply right-click on the database and check the properties, and in another method, we run a SQL query to return the file location for all the databases.