Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
this may be the only thing I can do but I wish there was a better answer. –  djangofan Jun 22 '10 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. –  Ally Reilly Jun 22 '10 at 19:05
add comment

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.

share|improve this answer
    
The "General" database properties are read-only from SSMS. –  djangofan Jun 22 '10 at 18:16
    
Sounds like you'll need to use an account with proper privs when connecting to the instance with SSMS –  Chris_K Jun 22 '10 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. –  djangofan Jul 6 '10 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) –  Chris_K Jul 6 '10 at 23:42
add comment

BTW, Moving SQL Server System Databases

share|improve this answer
    
im not having any trouble moving databases. what i am having trouble with is moving the installed "instance" to a different drive. –  djangofan Jul 6 '10 at 23:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.