Default Data/Log Folder for a SQL Instance?

  • Thread starter Thread starter Joe Cool
  • Start date Start date
J

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?
 
Joe Cool said:
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?
Click to expand...

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.
 
  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.
Click to expand...

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
 
Back
Top