Server Settings – Part 3 – Ramblings of a Crafty DBA

Tonight, I will be talking about default database paths that a DBA can change either manually or programatically.

The automated process is to use undocumented extended system stored procedures to read from and write to the registry. The manual process is to use the Server Properties dialog box in SQL Server Management Studio (SSMS).

I am going to start with the database settings page that contains the default data file and log file directories. This setting determines where the data files (*.mdf, *.ndf) and log files (*.ldf) are stored.

Just enter the new directories in the database settings page and press the okay button to save. The configured values depict what is the stored value to use in the future. If a change requires the database engine to be restarted, the configured value will not equal the running value.

The TSQL snippets below use the xp_regread and xp_regwrite extended system stored procedures. There are three different places in the registry to read the default data and log directories.

If the default paths have never been changed since installation, the setting is stored in the registry under the SQLDataRoot key. The actual full key is dependent upon the instance, version and edition of SQL Server.

-- 
-- After installation, default is both data/log in [SQLDataRoot]
--

-- Declare variable
DECLARE @OldPath1 nvarchar(260);

-- Read from the registry
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup',
  @value_name='SQLDataRoot',
  @value=@OldPath1 OUTPUT  

-- Show value  
SET @OldPath1 = @OldPath1 + '\Data';
SELECT @OldPath1 AS DefaultPath

-- After installation, default is both data/log in [SQLDataRoot]

-- Declare variable

DECLARE @OldPath1 nvarchar(260);

-- Read from the registry

EXEC master..xp_regread

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup',

@value_name='SQLDataRoot',

@value=@OldPath1 OUTPUT

-- Show value

SET @OldPath1 = @OldPath1 + '\Data';

SELECT @OldPath1 AS DefaultPath

If changes have been made for the default data or log file directories, use the DefaultData and DefaultLog registry key respectively for reading and writing. The TSQL snippet below reads the current settings from the registry.

--
-- If changes are made, use [DefaultData]
--

-- Declare variable
DECLARE @OldPath2 nvarchar(260);

-- Read from the registry
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',
  @value_name='DefaultData',
  @value=@OldPath2 OUTPUT  

-- Show value  
SELECT @OldPath2 AS DefaultData

--
-- If changes are made, use [DefaultLog]
--

-- Declare variable
DECLARE @OldPath3 nvarchar(260);

-- Read from the registry
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',
  @value_name='DefaultLog',
  @value=@OldPath3 OUTPUT  

-- Show value  
SELECT @OldPath3 AS DefaultLog

-- If changes are made, use [DefaultData]

-- Declare variable

DECLARE @OldPath2 nvarchar(260);

-- Read from the registry

EXEC master..xp_regread

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',

@value_name='DefaultData',

@value=@OldPath2 OUTPUT

-- Show value

SELECT @OldPath2 AS DefaultData

-- If changes are made, use [DefaultLog]

-- Declare variable

DECLARE @OldPath3 nvarchar(260);

-- Read from the registry

EXEC master..xp_regread

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',

@value_name='DefaultLog',

@value=@OldPath3 OUTPUT

-- Show value

SELECT @OldPath3 AS DefaultLog

I want standardize my servers to have all default directories stored underneath the C:\MSSQL\ directory. The TSQL snippet below writes the new settings to the registry for default data and log file directories.

--
-- Write new settings (data & log default paths)
--

-- Declare variable
DECLARE @NewPath1 VARCHAR(260);

-- Set the value  
SELECT @NewPath1 = 'C:\MSSQL\DATA';

-- Write 2 the registry
EXEC master.dbo.xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultData',
  @type = 'REG_SZ', 
  @value = @NewPath1; 


-- Declare variable
DECLARE @NewPath2 VARCHAR(260);

-- Set the value  
SELECT @NewPath2 = 'C:\MSSQL\LOG';

-- Write 2 the registry
EXEC master.dbo.xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultLog',
  @type = 'REG_SZ', 
  @value = @NewPath2;

-- Write new settings (data & log default paths)

-- Declare variable

DECLARE @NewPath1 VARCHAR(260);

-- Set the value

SELECT @NewPath1 = 'C:\MSSQL\DATA';

-- Write 2 the registry

EXEC master.dbo.xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',

@value_name='DefaultData',

@type = 'REG_SZ',

@value = @NewPath1;

-- Declare variable

DECLARE @NewPath2 VARCHAR(260);

-- Set the value

SELECT @NewPath2 = 'C:\MSSQL\LOG';

-- Write 2 the registry

EXEC master.dbo.xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',

@value_name='DefaultLog',

@type = 'REG_SZ',

@value = @NewPath2;

Last but not least, the default location for backup files (*.bak, *.trn) can not be set in the Server Properties dialog box in SSMS. However, it is stores in the registry under the BackupDirectory key. The TSQL snipet below reads the current setting.

-- The default location for backup files (*.bak & *.trn)

-- Declare variable
DECLARE @OldPath4 nvarchar(260);

-- Read from the registry
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',
  @value_name='BackupDirectory',
  @value=@OldPath4 OUTPUT  

-- Show value  
SELECT @OldPath4 AS BackupPath

-- The default location for backup files (*.bak & *.trn)

-- Declare variable

DECLARE @OldPath4 nvarchar(260);

-- Read from the registry

EXEC master..xp_regread

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER',

@value_name='BackupDirectory',

@value=@OldPath4 OUTPUT

-- Show value

SELECT @OldPath4 AS BackupPath

To complete the example, the default backup directory is changed to conform to the new standard.

-- Update default location for backup files (*.bak, *.trn)

-- Declare variable
DECLARE @NewPath3 VARCHAR(260);

-- Set the value  
SELECT @NewPath3 = 'C:\MSSQL\BACKUP';

-- Write 2 the registry
EXEC master.dbo.xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='BackupDirectory',
  @type = 'REG_SZ', 
  @value = @NewPath3;

-- Update default location for backup files (*.bak, *.trn)

-- Declare variable

DECLARE @NewPath3 VARCHAR(260);

-- Set the value

SELECT @NewPath3 = 'C:\MSSQL\BACKUP';

-- Write 2 the registry

EXEC master.dbo.xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',

@value_name='BackupDirectory',

@type = 'REG_SZ',

@value = @NewPath3;

A new backup task was started on the Boy Scouts of America (BSA) sample database that I have used in the past. The new default backup location is picked up by the dialog box in SSMS.

In summary, the xp_regread and xp_regwrite extended stored procedures can be used to change the default directory for data, log and backup files.

10 Thoughts to “Server Settings – Part 3”

Clorinda Jantz

February 2, 2012 at 5:58 pm

very interesting topic , outstanding post.

Tianna Mongiello

February 4, 2012 at 10:52 pm

Hi there, just became aware of your blog through Google, and found that it is really informative. I’ll be grateful if you continue this in future. Many people will be benefited from your writing. Cheers!

Oscar Dewer

February 5, 2012 at 6:24 pm

As a Newbie, I am permanently searching online for articles that can aid me. Thank you

senuke bonus

February 5, 2012 at 8:22 pm

Don’t aim for success if you want it; just do what you love and believe in, and it will come naturally.

Rachal Magrath

February 5, 2012 at 11:29 pm

Lovely website! I am loving it!! Will be back later to read some more. I am taking your feeds also

Vasiliki Frees

February 6, 2012 at 3:29 pm

I am thankful to find this website through google.

E-Zigarette

February 7, 2012 at 2:07 am

I just couldn’t leave your website before letting you know that I actually enjoyed the useful details you offer to your visitors…

clayton clavette

February 7, 2012 at 7:41 am

Bookmarked this post and look forward to coming back again. Great job!

Dallas Murat

February 7, 2012 at 10:45 am

Thank you for helping out, fantastic info.

Hermine Bruyn

February 22, 2012 at 8:40 pm

I appreciate all the work you all have put into your blog! I’m going to Tweet this out to my followers… Definitely worth repeating!

Server Settings – Part 3

Related posts

10 Thoughts to “Server Settings – Part 3”

Leave a Comment Cancel reply

Related posts

Staying Connected

Transferring Logins Between Servers

Page Anatomy – Part 2

10 Thoughts to “Server Settings – Part 3”

Leave a Comment Cancel reply