How to Find the Data Directory of SQL Server 2005 in a Script

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.

Reference:

http://technet.microsoft.com/en-us/library/ms143547(SQL.90).aspx

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).

Advertisements

One thought on “How to Find the Data Directory of SQL Server 2005 in a Script

  1. Thank you for the insight. My problem is a little bit more complicated,

    I want to do the same thing as you did BUT for a remote sql server. is accessing the registry key on the remote server the only option?

    i appreciate any feedback.

    GA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s