In versions of SQL Server prior to 2005 finding the directory where SQL Server creates its databases was pretty easy since there was a registry key named after the instance of SQL Server and a value that specified the path.
With SQL Server 2005 Microsoft changed everything, making it a challenge to retrieve the correct data directory from a build script. The solution we had in our build script turned out to be wrong, so this afternoon I discovered the correct way to do it.
SQL Server 2005 stores the information in subkeys and values of a key named something like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1. While it usually is MSSQL.1 it can easily be something else if there are more than one instance of SQL Server installed. So which instance is the right one?
We could look at the default value of the key MSSQL.x which contains the name of the instance, but it’s a little cumbersome to loop through all the instances. The key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL contains a value named like our instance, in our case SILFW containing a data value of MSSQL.1.
Once we have that information we can look up the value DefaultData under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer to find the data directory where SQL Server creates new databases.
The other question I discovered the answer for was how to change the default data directory. Ok, now that I know the registry key and value I can change it there, but it’s also possible to do it through SQL Server Management Studio: right click on Server, Properties, Database Settings and change the Database default location.
Edit (2008-09-04): Note that the DefaultData value is only set if the default database directory got changed. Otherwise this value doesn’t exist. To make the script work in all cases you should check for the existance of this value. If it doesn’t exist you can get the database directory from the value SQLDataRoot under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Setup (possibly appending \Data to the value you retrieve).