October 12, 2015

MS SQL Server - Find physical folder path for data files using T-SQL

While deploying my application, I want to make it simplify for user to create / restore / drop databases. So I plan to automate these tasks into script and just provide a simple UI to accomplish these tasks with simple clicks. I need to find out the physical path where my database files are being stored. The following query will give you the physical path to store database's mdf and ldf files for your current SQL Server's instance.
SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) PhysicalPath
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Sample Output:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008MYINSTANCE\MSSQL\DATA\

Here we retrieve the mdf file path for master database, i.e. database_id = 1, and file_id = 1 (file_Id = 2 will give you ldf file path, folder path will be same). Then we extract only the folder path by using substring() function.

No comments:

Post a Comment