Beeravolu Technologies

We provide tech review and share knowledge in the form of blog post

Database

How to find database file location in SQL Server

By | Published

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).

  • 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, and SQL Server 2019.

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\

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 as shown in the below screenshot.

database data file location
SQL Server database Properties

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

find database file location in sql server
Database file location in SQL Server

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

The result of the query is shown below.

locate database file
database file location in SQL Server

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.

Latest posts