Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Setting SQL Server Default Locations

By Wayne Sheffield,

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;

Resources:

How to find & change the default locations in SQL Server.docx
Total article views: 5461 | Views in the last 30 days: 6
 
Related Articles
FORUM

Database Default file Locations

Database Default Locations in Database Setting Under Server Properties

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

FORUM

default data/log file location

query to get default data/log location

FORUM
FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones