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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- -- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
-- -- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- -- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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.
very interesting topic , outstanding post.
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!
As a Newbie, I am permanently searching online for articles that can aid me. Thank you
Don’t aim for success if you want it; just do what you love and believe in, and it will come naturally.
Lovely website! I am loving it!! Will be back later to read some more. I am taking your feeds also
I am thankful to find this website through google.
I just couldn’t leave your website before letting you know that I actually enjoyed the useful details you offer to your visitors…
Bookmarked this post and look forward to coming back again. Great job!
Thank you for helping out, fantastic info.
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!