2012-04-09

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).

No comments: