Get the Data and Log File location for every database on a SQL Server

On 02 May, 2015

MS SQL DBA

By : Charith Silva

No Comments

Below SQL Script will help you to find current location of Data and Log File of the Database. It shows the file location of every database on a SQL Server instance.

SELECT [Database] as [Database Name],
[1] as MDF_File_Location,
[2] as Log_File_Location
FROM
(
SELECT [Database], [1], [2]
FROM
(
SELECT d.name [Database],
mf.file_id,
mf.physical_name AS [Path]
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
) AS st
PIVOT
(
MAX([Path]) FOR file_id IN ([1], [2])
) AS PT
) a

SELECT [Database] as [Database Name],

[1] as MDF_File_Location,

[2] as Log_File_Location

FROM

(

SELECT [Database], [1], [2]

FROM

(

SELECT d.name [Database],

mf.file_id,

mf.physical_name AS [Path]

FROM sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

) AS st

PIVOT

(

MAX([Path]) FOR file_id IN ([1], [2])

) AS PT

) a

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva