Setting SQL Server Default Locations

SQL Server has default directory locations for where to place backups, data files, and log files. In the case of backups, this allows you to specify a backup with just a file name (from T-SQL), and the backup will go to this directory. When using the GUI tool, it will use this as the default directory. For data/log files, new databases will default to this location unless specified otherwise. Here is how you find and change these default locations in SQL Server:

Default data file / log file location.

I will show the different ways you can check or change this in various versions of SQL Server.

SQL Server 2008, SQL Server 2005

• Connect to the server in SSMS.
• In the Object Explorer window, right-click the server and select “Properties”.
• Under “Select a page”, click on “Database properties”.
• The default data/log file locations can now be set.

SQL Server 2008

• Connect to the server in SSMS.
• In the Object Explorer window, right-click the server and select “Facets”.
• Change the Facet to “Server Settings”
• The default data/log file locations can now be set.

SQL Server 2000

• Connect to the server in Enterprise Manager.
• Right-click the server, and select “Properties”.
• Click on the “Database Properties” tab.
• The default data/log file locations can now be set.

SQL Server 2008, SQL Server 2005, SQL Server 2000

• Open the registry editor (regedit.exe)
• Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer (the next-to-last level will vary based on the version of sql, the number of instances, and the instance name, but it will be similar)
• Edit and save the DefaultData / DefaultLog keys.

Default backup directory

Here I will show how to find or change the default backup directory in various versions of SQL Server.

SQL Server 2008

• Connect to the server in SSMS.
• In the Object Explorer window, right-click the server and select “Facets”.
• Change the Facet to “Server Settings”
• The default backup file location can now be set.

SQL Server 2008, SQL Server 2005, SQL Server 2000

• Open the registry editor (regedit.exe)
• Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer (the next-to-last level will vary based on the version of sql, the number of instances, and the instance name, but it will be similar)
• Edit and save the BackupDirectory key.

So, you like doing everything in T-SQL? Since all of these are stored in the registry, you can use the xp_RegRead / xp_RegWrite extended stored procedures to read/write these values. Here is an example for reading all three locations from the registry for the instance that you are running on. This script has been tested on a default instance of SQL 2000, SQL 2005 and SQL 2008. I’ll leave it to you to figure out how to use xp_RegWrite to save values to the registry.

declare @ServerName sysname,
         @RegRootPath varchar(250),
         @InstanceKeysPath varchar(250),
         @InstanceKeyPath varchar(250),
         @BackupDir varchar(1000),
         @DefaultDataDir varchar(1000),
         @DefaultLogDir varchar(1000),
         @HKLM varchar(20),
         @SQLVer varchar(250),
         @iSQLVer tinyint;
set @SQLVer  = CONVERT(varchar(250),SERVERPROPERTY('ProductVersion'));
set @iSQLVer = CONVERT(tinyint,left(@SQLVer, CharIndex('.', @SQLVer)-1));
--@iSQLVer =  8 -->> SQL 2000
--@iSQLVer =  9 -->> SQL 2005
--@iSQLVer = 10 -->> SQL 2008
set @HKLM = 'HKEY_LOCAL_MACHINE';       
set @RegRootPath = 'SOFTWARE\Microsoft\';
if @iSQLVer > 8 set @RegRootPath = @RegRootPath + 'Microsoft SQL Server\';
set @InstanceKeysPath = @RegRootPath + 'Instance Names\SQL';
-- get the instance name. If default, use MSSQLSERVER
set @ServerName = @@SERVERNAME;
if CHARINDEX('\', @ServerName) > 0
   set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1, 250)
else set @ServerName ='MSSQLSERVER';
if @iSQLVer > 8 begin
  -- get the path for this instance
  execute master..xp_regread @HKLM, @InstanceKeysPath, @ServerName, @InstanceKeyPath OUTPUT;
  set @InstanceKeyPath = @RegRootPath + @InstanceKeyPath + '\MSSQLServer'
end 
else begin
   set @InstanceKeyPath = @RegRootPath + @ServerName +'\MSSQLServer'
end;
-- read the directory locations
execute master..xp_regread @HKLM, @InstanceKeyPath, 'BackupDirectory', @BackupDir OUTPUT;
execute master..xp_regread @HKLM, @InstanceKeyPath, 'DefaultData', @DefaultDataDir OUTPUT;
execute master..xp_regread @HKLM, @InstanceKeyPath, 'DefaultLog', @DefaultLogDir OUTPUT;
select @BackupDir, @DefaultDataDir, @DefaultLogDir;