Changing the default data location when SQL Server won’t start

So I suppose default data locations are important?  Updates were applied to a server recently and someone included a sql server patch that upgraded master and msdb.  SQL Server wouldn’t start after a reboot, and it was a mystery as to what had happened.  In Event Viewer, we were presented with the following:

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘G:\Logs\temp_MS_AgentSigningCertificate_database_log.LDF’.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘G:\Data\temp_MS_AgentSigningCertificate_database.mdf’.

So, G:\Data and G:\Logs were mount points that were set up as default locations for databases.  In order to resolve this issue, we needed to change the default data location to be the location where master was, which was still in the default installation location.  For me, that was:

G:\MSSQL10_50.<InstanceName>\MSSQL\DATA

To do this, we have to dive into the registry (regedit). 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<Instance Name>\MSSQLServer

Key: DefaultData – needs to be changed from G:\Data to G:\MSSQL10_50.<Instance Name>\MSSQL\DATA

Key: DefaultLog – needs to be changed from G:\Logs to G:\MSSQL10_50.<Instance Name>\MSSQL\DATA

The “SQLPath” key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<Instance Name>\Setup is also set to the location where the installation has taken place: G:\MSSQL10_50.<Instance Name>\MSSQL

After changing these keys, I was able to start the sql service.  It takes a few seconds for the upgrade scripts to then complete.  Then, you have your instance back.  Now to run a query to see which instances have had their default locations for data and logs changed…