Get Backup Directory with ADO.NET

About 1½ year ago I made a entry on this blog on how to get the SQL Server Backup Directory by using the undocumented stored procedure "[master].[sys].[xp_instance_regread]".
Today I needed the path name in a PowerShell script, and I also wanted to call the procedure correct.
This I have done by calling the procedure through ADO.NET as a stored procedure, not in a EXECUTE statement as dynamic SQL.
$ServerName = '(local)'
$cnnStr = "Data Source=$ServerName;Integrated Security=SSPI;Application Name=SqlBackupFolder"
$Cnn = New-Object System.Data.SqlClient.SqlConnection $cnnStr
$Cmd = New-Object System.Data.SqlClient.SqlCommand
$Cmd.Connection = $Cnn
$Cmd.CommandText = '[master].[sys].[xp_instance_regread]'
$Cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$Cmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'
$Cmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@key'].Value = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLSERVER'
$Cmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@value_name'].Value = 'BackupDirectory'
$Cmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Output
$Cnn.Open()
$_RowCount = $Cmd.ExecuteNonQuery()
$Cnn.Close()
$SqlBackupFolder = $Cmd.Parameters['@value'].Value
":: SQL Server Backup Folder = '$SqlBackupFolder'."
The answer from the script is like
:: SQL Server Backup Folder = 'C:\MSSQL\Backup'.

It is possible to reduce the number of lines in the script, but this way I can use the call of the procedure for other information than the Backup Directory.

If you plan to reuse the Command object, you should consider to remove the Parameters ($Cmd.Parameters.Clear()) and reset the CommandType ($Cmd.CommandType = [System.Data.CommandType]::Text).

Get SQL Server root folder with xp_instance_regread

I often use the standard folders of SQL Server, like the folder "Log" with SQL Server Error Log files and my SQL Server Agent job log files or the folder "JOBS" with my job script files.
These folders are subfolders to the SQL Server root folder, so when I have the root folder, I have the rest.
"One folder to hold them all"
Also I would like to be able to get the root folder on named instances and non-standard installations.
By using the undocumented stored procedure "[master].[dbo].[xp_instance_regread]", I have a useful solution to my need.
Actually the procedure is placed in the schema "sys" on newer SQL Server versions, but by calling the "dbo" schema the same statement works on SQL Server 2000 and 2012.

DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key = N'SOFTWARE\Microsoft\MSSQLSERVER\Setup',
  @value_name = N'SQLDataRoot',
  @value = @root_folder OUTPUT;
SELECT @root_folder AS [root_folder];

The answer could be
P:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL

It does work on SQL Server 2000 and named instances.

If I place the statement in a PowerShell Here-String in a SQL Server Agent PowerShell job step and use the cmdlet Invoke-SqlCmd, I get the root folder without knowing the name of the server or the instance.

EXECUTE [msdb].[dbo].[sp_add_jobstep]
...,
@subsystem=N'PowerShell',
@command=N'$sql = @"
DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
  @rootkey = N''HKEY_LOCAL_MACHINE'',
  @key = N''SOFTWARE\Microsoft\MSSQLSERVER\Setup'',
  @value_name = N''SQLDataRoot'',
  @value = @root_folder OUTPUT;
SELECT @root_folder AS [root_folder];
"@
Invoke-SqlCmd -Query $sql',
...

Very nice I think, because it makes it possible to use the same job definition on all SQL Server database instances. E.g. in standard backup jobs.

Also I can use the Root Folder path direct in PowerShell:
Import-Module 'SQLPS'
$sql = @"
DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key = N'SOFTWARE\Microsoft\MSSQLSERVER\Setup',
  @value_name = N'SQLDataRoot',
  @value = @root_folder OUTPUT;
SELECT @root_folder AS [root_folder];
"@
$RootFolder = $(Invoke-SqlCmd -Query $sql).root_folder
":: MSSQL Root Folder = '$RootFolder'."
In this case the answer is
:: MSSQL Root Folder = 'P:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL'.