Knowledge Base

Changing the default data location used by SQL Backup

Category: How do I?
Date: 06 Aug 2009
Product: SQL Backup
Versions: 5.1 and higher

When databases are restored using the SQL Backup user interface, a data file location is chosen for the database, rather than relying on the information retrieved from the backup file.

If the default data location needs to be changed, it is necessary to modify the registry on the SQL Server computer. Changing the default data location using the SQL Server settings (Management Studio) has no effect on where the data files will be created by default. To change the path so that SQL Backup will restore the database files to the desired location, use the Windows registry editor to set a STRING value.

All editions make use of HKLM\Software\Microsoft\Microsoft SQL Server

SQL 2000 instances have a key in it named after the instance name. In there are some sub keys, MSSQLServer/ and Setup/ in which the config strings can be found.
SQL 2005 instances have keys that look like MSSQL.X, where X is a number. The instance name is held in a string on this key. The config strings are in Setup/
SQL 2008 instances have keys that look like MSSQL10.[InstanceName]. Setup/ has the configs.

Examples for default data location

SQL Server 2000 HKLM\Software\Microsoft\Microsoft SQL Server\instancename\Setup\SQLDATAROOT
SQL Server 2005 HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\SQLDATAROOT
SQL Server 2008 HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.instancename\Setup\SQLDATAROOT

(instancename = MSSQLSERVER for default instance, X is the instance ID)