1

Does anyone know a way to change the SQL Server 2005 default instance "Root directory" without re-installing the instance? I just installed a RAID5 SAN drive and I want the backups of the SQL server to be taken on that drive.

I believe I would have to hack the registry and change these keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\FullTextDefaultPath HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLBinRoot etc.

Since there are so many keys that need to be hacked I think re-installing the instance might be the way to go. Am I right?

Improve this question

3 Answers 3

Reset to default
2

Don't think you can change the root directory as this is where the binaries are but you can change the default backup location. This is done in the registry, you will need to navigate to the key below (mine is 2008 R2 but should be the similar on 2005). The last part of the key will be different on yours.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER

You can then change the key name BackupDirectory.

Improve this answer
2
  • this may be the only thing I can do but I wish there was a better answer. Commented Jun 22, 2010 at 18:25
  • The only other option I found was using xp's to change the registry value. Same result just a different method. I don't think there is any real risk in changing this key. It's just changing the value that is read by sql server. BTW this will just change the default location for backups. You will still need to change the backup jobs or backup device dependant on how you have it set up. Commented Jun 22, 2010 at 19:05
0

To change the default backup directory you have to do some registry hacking. Find this key and modify the value to the new drive\path:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.*x*\MSSQLServer

(change the "x" to match what you find your registry)

You can change default data and log drive locations the easy way. Just fire up SSMS, right-click on the instance name and go to Properties. Click on Database Settings and update the defaults.

Improve this answer
4
  • The "General" database properties are read-only from SSMS. Commented Jun 22, 2010 at 18:16
  • Sounds like you'll need to use an account with proper privs when connecting to the instance with SSMS Commented Jun 22, 2010 at 18:24
  • if I start SQL in the single user admin mode i think this could be done but I discovered that there are far more than just 1 registry key that has to be changed and so your answer isnt complete. Commented Jul 6, 2010 at 23:33
  • Will you be sharing the "far more" keys with us? I only changed one when I tested my answer and didn't have any issue. (remember, you don't need to hack reg. to change default data / log locations) Commented Jul 6, 2010 at 23:42
0

BTW, Moving SQL Server System Databases

Improve this answer
1
  • im not having any trouble moving databases. what i am having trouble with is moving the installed "instance" to a different drive. Commented Jul 6, 2010 at 23:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.