Tuesday, February 1, 2011

Phyical MDF and LDF location in SQL Server

select * from [master].[sys].[master_files]

On executing the query above, we will get the below result set,

Now, lets tune up the query further, I am trying to retrieve data files physical path for my master database.

DECLARE @sql_path NVARCHAR(256) ;

SELECT @sql_path = 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 ;
print @sql_path

The output is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\”

No comments:

Post a Comment