{"id":1378,"date":"2012-01-11T04:27:59","date_gmt":"2012-01-11T04:27:59","guid":{"rendered":"http:\/\/craftydba.com\/?p=1378"},"modified":"2017-10-12T12:26:06","modified_gmt":"2017-10-12T12:26:06","slug":"server-settings-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1378","title":{"rendered":"Server Settings – Part 3"},"content":{"rendered":"

Tonight, I will be talking about default database paths that a DBA can change either manually or programatically.<\/p>\n

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<\/a>).<\/p>\n

I am going to start with the database settings page<\/a> 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.<\/p>\n

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.<\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

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. <\/p>\n

\"\"<\/a><\/p>\n

\r\n-- \r\n-- After installation, default is both data\/log in [SQLDataRoot]\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @OldPath1 nvarchar(260);\r\n\r\n-- Read from the registry\r\nEXEC master..xp_regread \r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\Setup',\r\n  @value_name='SQLDataRoot',\r\n  @value=@OldPath1 OUTPUT  \r\n\r\n-- Show value  \r\nSET @OldPath1 = @OldPath1 + '\\Data';\r\nSELECT @OldPath1 AS DefaultPath\r\n<\/pre>\n<\/p>\n
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.<\/p>\n
\r\n--\r\n-- If changes are made, use [DefaultData]\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @OldPath2 nvarchar(260);\r\n\r\n-- Read from the registry\r\nEXEC master..xp_regread \r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLSERVER',\r\n  @value_name='DefaultData',\r\n  @value=@OldPath2 OUTPUT  \r\n\r\n-- Show value  \r\nSELECT @OldPath2 AS DefaultData\r\n\r\n--\r\n-- If changes are made, use [DefaultLog]\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @OldPath3 nvarchar(260);\r\n\r\n-- Read from the registry\r\nEXEC master..xp_regread \r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLSERVER',\r\n  @value_name='DefaultLog',\r\n  @value=@OldPath3 OUTPUT  \r\n\r\n-- Show value  \r\nSELECT @OldPath3 AS DefaultLog\r\n<\/pre>\n<\/p>\n
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.<\/p>\n
\r\n--\r\n-- Write new settings (data & log default paths)\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @NewPath1 VARCHAR(260);\r\n\r\n-- Set the value  \r\nSELECT @NewPath1 = 'C:\\MSSQL\\DATA';\r\n\r\n-- Write 2 the registry\r\nEXEC master.dbo.xp_regwrite\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='DefaultData',\r\n  @type = 'REG_SZ', \r\n  @value = @NewPath1; \r\n\r\n\r\n-- Declare variable\r\nDECLARE @NewPath2 VARCHAR(260);\r\n\r\n-- Set the value  \r\nSELECT @NewPath2 = 'C:\\MSSQL\\LOG';\r\n\r\n-- Write 2 the registry\r\nEXEC master.dbo.xp_regwrite\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='DefaultLog',\r\n  @type = 'REG_SZ', \r\n  @value = @NewPath2; \r\n<\/pre>\n<\/p>\n
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.<\/p>\n
\r\n-- The default location for backup files (*.bak & *.trn)\r\n\r\n-- Declare variable\r\nDECLARE @OldPath4 nvarchar(260);\r\n\r\n-- Read from the registry\r\nEXEC master..xp_regread \r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLSERVER',\r\n  @value_name='BackupDirectory',\r\n  @value=@OldPath4 OUTPUT  \r\n\r\n-- Show value  \r\nSELECT @OldPath4 AS BackupPath\r\n<\/pre>\n<\/p>\n
To complete the example, the default backup directory is changed to conform to the new standard. <\/p>\n
\r\n-- Update default location for backup files (*.bak, *.trn)\r\n\r\n-- Declare variable\r\nDECLARE @NewPath3 VARCHAR(260);\r\n\r\n-- Set the value  \r\nSELECT @NewPath3 = 'C:\\MSSQL\\BACKUP';\r\n\r\n-- Write 2 the registry\r\nEXEC master.dbo.xp_regwrite\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='BackupDirectory',\r\n  @type = 'REG_SZ', \r\n  @value = @NewPath3;\r\n<\/pre>\n<\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[302,284,300,298,299,303,304,12,15,28,301,285,286],"class_list":["post-1378","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-backupdirectory","tag-database-admin","tag-default-backup-directory","tag-default-data-directory","tag-default-log-directory","tag-defaultdata","tag-defaultlog","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-sqldataroot","tag-xp_regread","tag-xp_regwrite"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1378"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1378\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1378"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}