question

ButchieZ avatar image
ButchieZ asked

Default Data, log and backup location changed after reboot

Has anyone ever seen this or know why this would happen? I have a 5 year old windows 2003 cluster running a named instance of SQL Server 2005 enterprise. During the install of SQL the log and data files were created on the H drive. (Making H:\\MSSQL.1\\MSSQL\\Data the default for both data and logs and H:\\MSSQL.1\\MSSQL\\Backup the default location for backups) After the install we run a script to update the Reg to change the default locations as following: Data = E:\\MSSQL.1\\MSSQL\\Data LOG = F:\\MSSQL.1\\MSSQL\\Data Backup = G:\\MSSQL.1\\MSSQL\\Backup Script 
--Set backup loaction in the registry  
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'BackupDirectory', 'REG_SZ', 'G:\\MSSQL.1\\MSSG\\Backup'    
--Set data loaction in the registry  
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'DefaultData', 'REG_SZ', 'E:\\MSSQL.1\\MSSG\\Data'  
--Set log loaction in the registry  
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'DefaultLog', 'REG_SZ', 'F:\\MSSQL.1\\MSSG\\Data'
Now last week the OS on the servers in the cluster were patched and after the reboot all of the default locations where changed back to: Data = H:\\MSSQL.1\\MSSQL\\Data LOG = H:\\MSSQL.1\\MSSQL\\Data Backup = H:\\MSSQL.1\\MSSQL\\Backup These servers have been patch several times throughout the years and this is the first time it has ever happened. We have seen it switch Server Authentication a few times from Mixed to Windows.
databasepatchingdefault
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Any updates...?
0 Likes 0 ·

2 Answers

·
ThomasRushton avatar image
ThomasRushton answered
It sounds to me as though one of the servers in the cluster hasn't been set up the same way as the others. It might be worth checking the registry settings for all machines in the cluster. As I wrote elsewhere (), >In order to find (and change) this setting, we need to dive into the registry, to (deep breath): HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL >In there, you’ll find a value for each instance defined on your server. Look at the Data value, and then navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer Check that the appropriate directories are set up correctly across all cluster servers and their instances of SQL Server within the clusters.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
Writing to the registry probably only did so on the node that was active at the time. On a failover it would be using the registry from a different node. Make sure that if you do registry work that all nodes are kept in synch.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.