Tag: xp_instance_regread

DBA Utilities, SQL

How to read and write backup directory in SQL Server
There is a requirement for me to change the backup directory of a SQL Server instance. Here are few tips related to the sobject and hope it will be useful as a future reference for all of us.

How to read the value of BackupDirectory? 

DECLARE @path NVARCHAR(4000)

EXEC master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            @path OUTPUT, 
            'no_output'

How to write/set a new value of BackupDirectory? 

EXEC master.dbo.xp_instance_regwrite
            N'HKEY_LOCAL_MACHINE',
            N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            REG_SZ,
            N'\\xx.xxx.xx.xxx\C$\data\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup';

Few other important information: 

DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)

--Installation Root Info
EXEC master.dbo.xp_instance_regread 
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\Setup',
	N'SQLDataRoot',
@SQLDataRoot OUTPUT
Select @SQLDataRoot

-- SQL Data file Info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
@DefaultData OUTPUT
Select @DefaultData

-- SQL Default Default Log file info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
@DefaultLog OUTPUT
Select @DefaultLog
xp_instance_regread & xp_instance_regwrite are undocumented features, so it may be deprecated any time, however, can be used for non-production or DBA specific tasks at own discretion.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!