SQL SERVER – Find Location of Data File Using T-SQL

While preparing for the training course of Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning, I needed to find out where my database files are stored on my hard drive. It is when following script came in handy to find the location of the data file using T-SQL.

SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Resultset:

DataFileLocation
--------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
(1 row(s) affected)

Above script provided exact location of where my data files of master database is. In fact, you can use it to find any other path of database as needed. If you want, you can change the master. mdf to any other file and you can easily find the location of the other database as well.

Here are few other blog posts related to this topic which you may find interesting:

• What is Copy Only Backup in SQL Server? – Interview Question of the Week #128
• SQL SERVER – Maintenance Plan – Backup Files Not Getting Deleted
• How to Get Status of Running Backup and Restore in SQL Server? – Interview Question of the Week #113
• SQL SERVER – Fix Error – Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512

I personally feel this is a very handy trick to find location of data file using T-SQL. If you have any other ticks available, do post in the comment and I will publish the blog post with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)