Default Data/Log Folder for a SQL Instance?

[Joe Cool]

I know that the default data and log folders for an instance are
stored in the registry.

I know how to obtain a list of all available SQL Instances on the
network using the SqlDataSourceEnumerator static class.

But is there a .NET way of getting the default data/log folders for
any of the returned instances without accessing the registry of the
server the instance resides on?

 

[Alberto Poblacion]

I know that the default data and log folders for an instance are
stored in the registry.

I know how to obtain a list of all available SQL Instances on the
network using the SqlDataSourceEnumerator static class.

But is there a .NET way of getting the default data/log folders for
any of the returned instances without accessing the registry of the
server the instance resides on?

From your program you could send a SQL query to the server so that it
reads and returns the registry value:
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
@RegPathParams, @Arg, @Param OUTPUT

This is the technique that SQL Server Management Studio uses when it
displays the Data/Log folders in the server properties window. You can
verify this by means of SQL Profiler: start a trace and then open the server
properties in SSMS. Then go to the trace to see the complete block of
queries that was used for fetching those data. You can issue the same
queries from a client computer, as long as you can open a SqlConnection to
the server with a sufficiently privileged account.

 

[Joe Cool]

  From your program you could send a SQL query to the server so that it
reads and returns the registry value:
     exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
@RegPathParams, @Arg, @Param OUTPUT

   This is the technique that SQL Server Management Studio uses when it
displays the Data/Log folders in the server properties window. You can
verify this by means of SQL Profiler: start a trace and then open the server
properties in SSMS. Then go to the trace to see the complete block of
queries that was used for fetching those data. You can issue the same
queries from a client computer, as long as you can open a SqlConnection to
the server with a sufficiently privileged account.

Thanks for the tip! I didn't even consider using the profiler to see
how SQL Server Management Studio obtains these values. Here is the
exact result from the profiler:

declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software
\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile
OUTPUT

declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software
\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog
OUTPUT