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
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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer
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.
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=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer’,
@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=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer’,
@value_name=‘BackupDirectory’,
@type=‘REG_SZ’,
@value=‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup’