dbi services Blog
Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.
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_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQLServer\DefaultData
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
...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.
If you have any question or concerns, please feel free to contact me.