How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry.

To read more about these options read SQL Server - Find Default Data location and Default Log Location ....    

For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and include the code as part of other DBA scripts used to manage SQL Server.

An example of using the SERVERPROPERTY('InstanceDefaultPath') and SERVERPROPERTY('InstanceDefaultLogPath') :

select

 Default_Data_Path = serverproperty('InstanceDefaultDataPath'),

 Default_Log_Path = serverproperty('InstanceDefaultLogPath')

Typically this information is used to discover the target path of the data and log file when the CREATE DATABASE is executed. If no paths are detailed in the CREATE DATABASE statement - SQL Server will place the files on the Default Data and Log files.

It could also be used to discover where the data files are maintained - if troubleshooting  a full disk or a performance issue. Although you might be better off using sys.master_files and list all database files  

Related articles

SQL Server Install Checklist

MS SQL 2016 Management Studio Silent Install