How to change the default location of SQL Server Backups

9 Dec 2010 3-minute read Al Eardley
Data PlatformOn-Prem
SQL Server

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.

Table of Contents


Posts in this Series


Comment on this post: