MS SQL Server - Physical folder path using master.dbo.xp_regread

In previous post, we found a way to get the location path to store database files for SQL Server. Another way to find the physical path is master.dbo.xp_regread or master.dbo.xp_instance_regread. You can use these procedures to find the physical path where MS SQL Server stores database files.

xp_regread reads the exact literal registry path you specify, while xp_instance_regread reads(or can transform) the path you specify so that it matches the instance of SQL Server that you're currently using.

Lets see an example:

declare @MasterMdfFile1 nvarchar(512)
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterMdfFile1 output

select @MasterMdfFile1 = substring(@MasterMdfFile1, 3, 255) --removes extra '-d' in-front of path

declare @MasterMdfFile2 nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterMdfFile2 output

select @MasterMdfFile2 = substring(@MasterMdfFile2, 3, 255) --removes extra '-d' in-front of path
select @MasterMdfFile1 as MasterMdfFile1, @MasterMdfFile2 as MasterMdfFile2

Sample Output:
MasterMdfFile1    MasterMdfFile2
NULL                             C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2SP2\MSSQL\DATA\master.mdf

Here we see that master.dbo.xp_regread returns NULL to @MasterMdfFile1 variable, because it could not find the literal registry path we provided. But master.dbo.xp_instance_regread is showing the result because it converts the path that matches the current instance of SQL Server we are using.