SQL Server: How to find the default data path?

I have read a lot of SQL Server blog postings and articles in order to find the default data path. This post covers different SQL Server versions (SQL Server 2012, SQL Server 2014, SQL Server 2008, SQL Server 2005) and provides a generic script with different methods.

Search with SERVERPROPERTY

Since SQL Server 2012, we can use the parameter “InstanceDefaultDataPath” in the T-SQL command SERVERPROPERTY.
More information on msdn here.

Test with SQL server 2008

Test with SQL Server 2012

Notice that we did not retrieved any error number when the SQL Server 2008 command failed.
It is very important to test if the returned path is NULL and not if we have an error number.

Search via registry key

To search this information in the registry, you need to have a registry path for the instance.
The first step is to look for the default registry path. This information is available in the key:

HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/Instance Names/SQL

You may have multiple instances, as shown in my lab screenshot above. The best way is to enumerate all instance keys and select the right one.
For this, I have to create a temporary table and insert the instance name and the registry path in it.
Then, I have to select the registry path corresponding to the instance with @@SERVERNAME.
Be careful: With a default instance, @@SERVERNAME is the server name and in the registry, you have “MSSQLSERVER” as instance name.

In this script, all cases are covered. The key to search the information is:

HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerMSSQLServerDefaultData

Test with SQL server 2005 via a default instance

Test with SQL Server 2014

I added this method to the first method with SERVERPROPERTY:

In my final script, I have added a DEBUG mode to get more information and display the SQL Server version, the detection mode, and the data path.
But if the key does not exist, we have no error message, like in the previous search. In this case, we must search for another method.

Search within an existing user-database

REVERSE(STUFF(REVERSE(@default_data_path),1,CHARINDEX('',REVERSE(@default_data_path)),''))

…gives us the path from first character to the last ”

But what if it is a new instance and we have no user-database yet?

Search with the master database

The last possibility is to search for the physical name in the same way as for a user-database, but in this case for the master database.
The only change is that I know the database this time, so I don’t need to search for it.
I have completed my script with these 2 solutions:

Conclusion

You will find a summary in the table below for every SQL Server version

In this script, we have 4 ways to find the default data path.
Logically, the 2 first ways are available for 99% of your instances – the first two should work in most cases.
With just a couple of changes, we can have the script for the default log path.
Please find the script for the default data path here and the script for the default log path there.
If you have any question or concerns, please feel free to contact me.