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;