How to change the default location of SQL Server Backups
One task that I have found very useful, especially on development servers that have a multitude of databases that are created and backed up in an ad-hoc manner, is to change the default location of the backups.
This means that all servers can store ad-hoc backups in a location that conforms to corporate guidelines.
It is not possible in SQL Management Studio to make this change. It is possible to make tis change directly in the registry but it is not repeatable. Instead, this script uses two undocumented stored procedures: xp_regread and xp_regwrite.
The use of undocumented procedures are is not supported by Microsoft and they are prone to changes in their behaviour and availability in future releases.
Find the name of the instance
Because the location of the data that is going to be changed is in the registry, the first step is to find the location of the data within the registry. To do this we need the name of instance, as identified in the registry. There are two methods to do this:
Method 1 is to use an application such as Regedit and navigate through the registry entries to find the name of the instances:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server
Method 2 assumes that the location of the master database has not been changed:
SELECT * FROM master.sys.sysfiles WHERE fileid = 1
If this is the case then the instance name will be within the path of the filename:
D:SQLSharePoint2010**MSSQL10_50.SHAREPOINT2010**MSSQLDATAmaster.mdf
Use SQL to update the Registry
Once the instance has been identified it is possible to use the undocumented extended procedures to update the registry.
I have used local variables throughout the script to reduce the number of places where changes need to be made. I read the current value of the backup directory first and then only update the registry if it needs to be updated.
/**************************************************
Declare local variables
**************************************************/
DECLARE
@strBackupDirectoryCurrent nvarchar(128)
, @strBackupDirectoryNew nvarchar(128) = 'D:SQLSharePoint2010DBEMSSQLBackup'
, @strRegistryRootKey nvarchar(128) = 'HKEY_LOCAL_MACHINE'
, @strRegistryKey nvarchar(128) = 'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.SHAREPOINT2010MSSQLServer'
, @strRegistryValue nvarchar(128) = 'BackupDirectory'
/**************************************************
Get current location of backups
**************************************************/
EXEC master..xp_regread
@rootkey = @strRegistryRootKey
, @key = @strRegistryKey
, @value_name = @strRegistryValue
, @BackupDirectory = @strBackupDirectoryCurrent OUTPUT
IF @strBackupDirectoryCurrent IS NULL
BEGIN
PRINT 'Value not found for current backup directory'
PRINT '@strRegistryRootKey = ' + @strRegistryRootKey
PRINT '@strRegistryKey = ' + @strRegistryKey
PRINT '@strRegistryValue = ' + @strRegistryValue
END
ELSE
BEGIN
PRINT 'Current backup directory is "' + @strBackupDirectoryCurrent + '"'
IF @strBackupDirectoryCurrent = @strBackupDirectoryNew
BEGIN
PRINT 'Current backup directory the same as the new backupdirectory - No need to change it'
END
ELSE
BEGIN
/**************************************************
Update location of backups
**************************************************/
PRINT 'Changing backup directory to "' + @strBackupDirectoryNew + '"'
EXEC master..xp_regwrite
@rootkey = @strRegistryRootKey
, @key = @strRegistryKey
, @value_name = @strRegistryValue
, @type = 'REG_SZ'
, @value = @strBackupDirectoryNew
END
END
Other Uses
This script can be altered to allow the configuration of other server settings that are stored in the registry such as “DefaultData” and “DefaultLog”, however it is possible to edit these within MS Management Studio and they are set during the setup of an instance.