Problem
When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Unless specified, the backup location uses the same base path as your database files. In this tip we look at how to change the default backup location.
Solution
The directories for the default data files, log files and backups are stored in the system registry. With SSMS you had the ability to change the backup location, but this was removed from SSMS for SQL 2005 and was later put back into SSMS with a later release of SQL Server.
Using SSMS to Change Backup Location
Here is what SSMS for SQL Server 2005 looks like where you only have the Data and Log locations on the Database Settings page.
Here is what this looks like for SQL Server 2014. You can see the Backup location is available from SSMS, so if you have this option available you can change the default backup directory using SSMS.
Using Registry to Change Backup Location
If we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer 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'.
Using Extended Stored Procedures to Change Backup Location
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 (make sure the key value matches the correct SQL Server installation path):
DECLARE @BackupDirectory VARCHAR(100) EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', @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 the folder back to the default we can use the following command and specify the path in the value parameter:
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', @value_name='BackupDirectory', @type='REG_SZ', @value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup'
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='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', @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 highlighted below will show you the corresponding install name for the instance such as "MSSQL2", so you know which registry entry needs to be changed.
That's all there is to it. Save yourself some time and change the default backup location instead of changing it for each backup.
Next Steps
- If you are tired of changing the location for your backups each time you do a backup you can make this one time registry change and not have to select a the folder each time. In addition, this works the same if you use the T-SQL BACKUP commands. You will not need to specify the folder just the file name and the backups will work without a problem.
- Take a look at these other backup tips
