How to Find Location of Data and Log Files of all SQL Server Databases

To find location of database files for all databases in a SQL Server 2005 and later instance, we can use the view sys.master_files:

SELECT name AS Logical_File_Name, physical_name AS File_Location
FROM master.sys.master_files

This script provides exact location of all data files of all databases.

If you want to get a list of all data files and transaction log files of a SQL Server instance, and see what databases they belong to:

SELECT d.name AS Database_Name,
m.name AS Logical_File_Name,
m.physical_name AS File_Location
FROM sys.master_files m
INNER JOIN sys.databases d
ON (m.database_id = d.database_id)
ORDER BY 1, 2

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">