Fetching data from the registry -
DECLARE @path NVARCHAR(4000) EXECUTE [master].[dbo].[xp_instance_regread] , N'HKEY_LOCAL_MACHINE' , N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory' , @path OUTPUT , 'no_output' SELECT @path AS DefaultBackupDirectory
Result - C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
Writing data to the registry -
DECLARE @path NVARCHAR(4000) SET @path ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup' EXECUTE [master].[sys].[xp_instance_regwrite] N'HKEY_LOCAL_MACHINE' , N'Software\Microsoft\MSSQLServer\MSSQLServer' , N'BackupDirectory' , N'REG_SZ' , @path;
2nd example, using named parameters -
DECLARE @path NVARCHAR(4000) SET @path ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup'
EXECUTE [master].[sys].[xp_instance_regwrite] @rootkey = N'HKEY_LOCAL_MACHINE' ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer' ,@value_name = N'BackupDirectory' ,@type = N'REG_SZ' ,@value = @path;