To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\

(1 row(s) affected)

Default Log Path

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Management Studio, SQL Configuration, SQLServer, SQLServer 2012, Undocumented Functions