Blog - comments

Hi Stephane,

In fact at dbi services we have professional material ;-)

Samsung SSD 840 PRO Series

Gregory Steulet

I have a write throughput 10x lower than your test (is it the SSD?)

stephane (ebu)
You obtained :Running the test with following options: Number of threads: 64 Random number generator...
stephane (ebu)

I wrote a benchmark based on your own. I'm so far from your result (which is very good for a laptop)

stephane (ebu)
Hi Stephane,It depends, you can find the stats for MySQL 5.6 below:sysbench 0.5: multi-threaded sys...
Gregory Steulet
Blog Stephane Haby SQL Server: How to find the default data path?

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.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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_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

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

REVERSE(STUFF(REVERSE(@default_data_path),1,CHARINDEX('',REVERSE(@default_data_path)),'')) 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.

Rate this blog entry:

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.



  • Guest
    ilya Friday, 26 June 2015

    Thank you

    very useful information

Leave your comment

Guest Friday, 31 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter