I have already explain regarding Right Join in SQL Server,left join in SQL,Self Join in SQL Server, RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding Sql Server – Find installed path and details of Data and log file for database.

I have created a database in sql Server and saved it on another physical path which was not default. Now after a month I would be required to move this database on another server, but I didn’t remember on which place I have created. So I have found below query to find installed path and details of data and log file.

SELECT
DB_NAME(mf.database_id) AS databaseName,
name as File_LogicalName,
case
when type_desc = ‘LOG’ then ‘Log File’
when type_desc = ‘ROWS’ then ‘Data File’
Else type_desc
end as File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 as size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 as size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 as size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY num_of_Reads DESC

Another way to find physical path for single database:

Right click on the database for which you want physical path -> Properties -> Files.