How To View SQL Server Database File Locations

Native Solution

Netwrix Auditor for SQL Server

Steps

Start Microsoft SQL Server Management Studio (MSSMS).
On the File menu, clickConnect Object Explorer. In the Connect to Server dialog box:

In the Server type list box, select Database Engine.
In the Server name text box, type the name of the SQL cluster server.
In the Authentication list box, choose your SQL Server Authentication method and specify the user credentials. If you do not want to re-type the password every time you connect to the server, tick Remember password.

Click Connect.
Upon connection, click “New Query” and paste the following script into the query field:

SELECT
mdf.database_id,
  mdf.name,
  mdf.physical_name as data_file,
  ldf.physical_name as log_file,
  db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
  log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf
JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf
ON mdf.database_id = ldf.database_id

Click Execute (or hit the F5 key).
Review the list of server database files in the query execution results:

How to View SQL Server Database File Locations - Native Auditing

Click here to edit

Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server - State-in-Time -> SQL Server Databases.
Click View.

How to View SQL Server Database File Locations - Netwrix Auditor

Get Detailed SQL Server Database File Location Information in a Few Clicks

Microsoft SQL Server has become one of the most popular relational database management systems for small and large businesses alike. IT pros need to be able to say exactly where important SQL database files are stored, without delays or errors, for multiple reasons, such as to facilitate backups and recovery processes, to transfer files to another location, or to find the folder if a server instance was installed to a non-standard location.

SQL Server stores data using two file extensions:

MDF files are the data files that hold the data and objects such as tables, indexes, stored procedures and views.
LDF files are the transaction log files that record all transactions and the database modifications made by each transaction.

The default database file location for server instances depends on the version of the Microsoft SQL Server software:

SQL Server 2014 — C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
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\

You have two native options for finding out where the SQL server stores its database files: either right-click on the instance name in SQL Server Management Studio (SSMS) and navigate to the ‘Database Settings’ tab, or use a T-SQL query. SSMS allows you to check one database at a time, and queries require a fair amount of expertise and time to compose them.

With Netwrix Auditor for SQL Server, you can get a comprehensive report on SQL Server database file locations that includes a summary of all key settings in just a few clicks. You can also report and alert on configuration changes, such as modifications to important functions, schemas, functions, database options and object owners. Even better, Netwrix Auditor also provides deep visibility into permissions to your SQL instances, databases and other SQL objects, as well as reporting and alerting on changes to those permissions. With this powerful tool, you can spot and revert improper or malicious changes to configurations and permissions before they lead to downtime or a data breach.

Previous How-to

How to Check User Privileges in SQL Server

Next How-to

How to View SQL Server Database Properties

How to View SQL Server Database File Locations

Get Detailed SQL Server Database File Location Information in a Few Clicks