Changing the default backup directory for SQL 2005

I got tired of using regedit to do this (the value is in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\{your instance}\MSSQLServer\BackupDirectory) and write a quick script to do it. I found it really tedious to change this on so many servers and clusters. It'll work with all versions of SQL 2005 and Express AFAIK.

I've posted it for anyone else interested, and be aware that it does use the "undocumented" xp_instance_regread and xp_instance_regwrite extended stored procedures.


DECLARE @SmoDefaultFile nvarchar(512), @NewBackupDir nvarchar(250);
SET @NewBackupDir = N'\\dbs51ykf\SQL_Backups'; --THE NEW BACKUP DIRECTORY TO USE

--LIST THE CURRENT DIRECTORY
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@SmoDefaultFile OUTPUT;

SELECT @SmoDefaultFile as [Old Backup Directory];

--CHANGE THE DIRECTORY
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
REG_SZ,
@NewBackupDir;

--CONFIRM CHANGE
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@SmoDefaultFile OUTPUT;

SELECT @SmoDefaultFile as [New Backup Directory];