Infrastructure at your Service

Stéphane Haby

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.


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

If we have a user database, we can easily search within the path of an existing database.
The path is in the physical name from the first database file. he view sys.database_files helps us find the path but returns the full path name of the data file.
We must only select the path prior to the file name. The query…


…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:


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.


Leave a Reply

one × = 3

Stéphane Haby
Stéphane Haby

Delivery Manager