Get physical path to SQL Server 2005 data files

To get the path where SQL Server 2005 stores data files for databases, you can use the following T-SQL script:

USE master

DECLARE @data_path nvarchar(256)
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1)

SELECT @data_path AS DataPath

It uses the built-in “master” database, finds the physical path to it from the “master.sys.master_files” view, then takes a substring to get just the path portion.