Blog - comments

Hi goog article can we install avdf firewall with flat network if it's possible please let me know ?

Thilina
First, thank you for your interrest in this blog.Yes, the byte code will be interpreted each time bu...
BIEHLER Stephane
Pretty sure this is wrong:> already said that JVMs interprets the generated byte code - that's true...
Gs
Michael, great article, however, I would disagree on DRS/Host Affinity. You are legally only requir...
David Bradshaw
Hi lauri, db_file_multiblock_read_count is still used in exadata smartscan because it defines the si...
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.

 

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

DefaultDataPath02.png


Test with SQL Server 2012

 DefaultDataPath01.png

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


DefaultDataPath03.png

 

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.  


DefaultDataPath04.png

 

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


DefaultDataPath06.png


Test with SQL Server 2014


DefaultDataPath05.png

 

I added this method to the first method with SERVERPROPERTY:


DefaultDataPath07.png

 

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.
 
DefaultDataPath10.png
 
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)),''))

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


DefaultDataPath11.png

 

Conclusion

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


DefaultDataPath12.png

 

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

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.


MCSA  MCSE  mvp

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 24 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

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