How to View SQL Server Database File Locations

Native Methods to View SQL Server Database File Locations

The two native methods are either by using the SQL Server Management Studio (SSMS) or alternatively by using a T-SQL query. One disadvantage of using the SSMS method is that it allows you to check only one database at a time; however, a disadvantage of writing and executing a T-SQL query is that it requires expertise and time.

Using the SQL Server Management Studio (SSMS): This is done by right-clicking on the instance name and navigating to the Database Settings tab.

Using a T-SQL query:

1. Start Microsoft SQL Server Management Studio (MSSMS)
2. On the File menu, click Connect Object Explorer.
   • In the Server type list box, select Database Engine
   • ­In the Server name 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, check Remember password.
3. Click Connect
4. 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

5. Click Execute (or hit the F5 key).
6. Review the list of SQL database locations in the query execution results:

How Lepide can Help

With the Lepide Solution implemented into your organization, all aspects of SQL server can be audited with ease. All changes made to the configuration of SQL users, permissions, logins, triggers, databases, and other SQL elements are audited. The raw logs are aggregated into meaningful audit data, saved long-term in a SQL Server database, and displayed in pre-defined audit reports. You can customize these reports to create a long audit-trail to understand how a change was made and to track a specific critical change.

Here is an example of the All Database Object Changes Report from the Lepide SQL Server Auditor:

This report is run as follows:

• Select Lepide Auditor, Reports
• Select All Database Object Changes from SQL Server reports
• Specify a Date Range
• Select Generate Report

The report is generated and can be filtered, sorted and exported to CSV and PDF format.