Change SQL backup location
During the installation of a SQL instance, you’ll have the ability to define some folders
These locations can be defined:
- User database directory
- User database lof directory
- Temp DB directory
- Temp DB log directory
- Backup directory
Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.
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.
Open the registry tool REGEDIT and navigate to following key:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.BTSTPTST1MSSQLServer
Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you’ll need to change to set another default Backup Directory.
Changing the registry can also be done with a T-SQL query command. To do so, you’ll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.
Reading the falue in registry can be done by using this command:
DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’,
@key=‘SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.BTSTPTST1MSSQLServer’,
@value_name=‘BackupDirectory’,
@BackupDirectory=@BackupDirectory OUTPUT SELECT @BackupDirectory
This will result in something similar as this:
Changing the default folder can be done by using the following command
EXEC master..xp_regwrite
@rootkey=‘HKEY_LOCAL_MACHINE’,
@key=‘SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.BTSTPTST1MSSQLServer’,
@value_name=‘BackupDirectory’,
@type=‘REG_SZ’,
@value=‘C:Program FilesMicrosoft SQL ServerMSSQL10_50.BTSTPTSTMSSQLBackup’
