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;