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:SQLSharePoint2010MSSQL10_50.SHAREPOINT2010MSSQLDATAmaster.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.

   1: /**************************************************

   2: Declare local variables

   3: **************************************************/

   4: DECLARE

   5:     @strBackupDirectoryCurrent    nvarchar(128)

   6: ,    @strBackupDirectoryNew        nvarchar(128) = 'D:SQLSharePoint2010DBEMSSQLBackup'

   7: ,    @strRegistryRootKey            nvarchar(128) = 'HKEY_LOCAL_MACHINE'

   8: ,    @strRegistryKey                nvarchar(128) = 'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.SHAREPOINT2010MSSQLServer'

   9: ,    @strRegistryValue            nvarchar(128) = 'BackupDirectory'

  10:

  11: /**************************************************

  12: Get current location of backups

  13: **************************************************/

  14: EXEC master..xp_regread

  15:     @rootkey = @strRegistryRootKey

  16: ,    @key = @strRegistryKey

  17: ,    @value_name = @strRegistryValue

  18: ,    @BackupDirectory = @strBackupDirectoryCurrent OUTPUT

  19:

  20: IF @strBackupDirectoryCurrent IS NULL

  21: BEGIN

  22:     PRINT 'Value not found for current backup directory'

  23:     PRINT '@strRegistryRootKey = ' + @strRegistryRootKey

  24:     PRINT '@strRegistryKey = ' + @strRegistryKey

  25:     PRINT '@strRegistryValue = ' + @strRegistryValue

  26: END

  27: ELSE

  28: BEGIN

  29:     PRINT 'Current backup directory is "' + @strBackupDirectoryCurrent + '"'

  30:

  31:     IF @strBackupDirectoryCurrent = @strBackupDirectoryNew

  32:     BEGIN

  33:         PRINT 'Current backup directory the same as the new backupdirectory - No need to change it'

  34:     END

  35:     ELSE

  36:     BEGIN

  37:         /**************************************************

  38:         Update location of backups

  39:         **************************************************/

  40:         PRINT 'Changing backup directory to "' + @strBackupDirectoryNew + '"'

  41:         EXEC master..xp_regwrite

  42:             @rootkey = @strRegistryRootKey

  43:         ,    @key = @strRegistryKey

  44:         ,    @value_name = @strRegistryValue

  45:         ,    @type = 'REG_SZ'

  46:         ,    @value = @strBackupDirectoryNew

  47:

  48:     END

  49: END

  50:

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.