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