x

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.
more ▼

asked Nov 11, 2011 at 08:12 AM in Default

ButchieZ gravatar image

ButchieZ
11 1 1 1

Any updates...?
Nov 18, 2011 at 07:44 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.
more ▼

answered Nov 12, 2011 at 09:10 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

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 (http://thelonedba.wordpress.com/2010/11/09/on-master4idr-ckp-maintenance-plans-default-backup-directory-and-sql-server-configuration/),

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\<<instancename>>\MSSQLServer
Check that the appropriate directories are set up correctly across all cluster servers and their instances of SQL Server within the clusters.
more ▼

answered Nov 11, 2011 at 11:28 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x241
x7
x7

asked: Nov 11, 2011 at 08:12 AM

Seen: 1403 times

Last Updated: Nov 11, 2011 at 08:38 AM