Scenario proposal
When you install SQL Server the path for the installation is generally something such as the following: C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups. Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?
Solution
The directories for the default data files, log files and backups are stored in the system registry. As mentioned already you have the ability to change the default data and log directories using SQL Server Management Studio, by right clicking on the server name and selecting properties and navigating to the Database Settings page as shown below.
But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory. To find this we need to look in the registry.
If we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer or similar for your instance of SQL Server we can see the following information The highlighted line below shows the default directory for the backups for this one instance.
If you want to change the value you can edit the registry value and save the new location. So for this example let’s change it to ‘D:SQLBackups’.
If I do a backup using SQL Management Studio and click on “Add” for a new destination file the following screen shows the default folder has now changed to ‘D:SQLBackups’.
Another way to change the registry is to using the extended stored procedures XP_REGREAD and XP_REGWRITE.
To read the value from the registry you can issue the following command:
DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
@value_name=’BackupDirectory’,
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory
This will provide the following output, since we changed the value above directly in the registry.
If we want to change this back to the default folder we can use the following command
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
@value_name=’BackupDirectory’,
@type=’REG_SZ’,
@value=’C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBackup’
If we run this command again we can see that this has changed:
DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
@value_name=’BackupDirectory’,
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory
To determine where SQL Server is installed you can right click on the server name, select Properties. The root directory as highlighted below will show you the corresponding install name for the instance such as “MSSQL2” highlighted below, so you know which registry entry needs to be changed.
That’s all there is to it, so save yourself some time and change the default location.
Thanks for reading this article,
Next steps :
- Add this script to your database toolkit
- Share this with your colleagues because Sharing is Learning
- Comment below if you need any assistance