How do I find the data directory for a SQL Server instance?

It depends on whether default path is set for data and log files or not.

If the path is set explicitly at Properties => Database Settings => Database default locations then SQL server stores it at Software\Microsoft\MSSQLServer\MSSQLServer in DefaultData and DefaultLog values.

However, if these parameters aren't set explicitly, SQL server uses Data and Log paths of master database.

Bellow is the script that covers both cases. This is simplified version of the query that SQL Management Studio runs.

Also, note that I use xp_instance_regread instead of xp_regread, so this script will work for any instance, default or named.

declare @DefaultData nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData outputdeclare @DefaultLog nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog outputdeclare @DefaultBackup nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup outputdeclare @MasterData nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData outputselect @MasterData=substring(@MasterData, 3, 255)select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))declare @MasterLog nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog outputselect @MasterLog=substring(@MasterLog, 3, 255)select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))select     isnull(@DefaultData, @MasterData) DefaultData,     isnull(@DefaultLog, @MasterLog) DefaultLog,    isnull(@DefaultBackup, @MasterLog) DefaultBackup

You can achieve the same result by using SMO. Bellow is C# sample, but you can use any other .NET language or PowerShell.

using (var connection = new SqlConnection("Data Source=.;Integrated Security=SSPI")){    var serverConnection = new ServerConnection(connection);    var server = new Server(serverConnection);    var defaultDataPath = string.IsNullOrEmpty(server.Settings.DefaultFile) ? server.MasterDBPath : server.Settings.DefaultFile;    var defaultLogPath = string.IsNullOrEmpty(server.Settings.DefaultLog) ? server.MasterDBLogPath : server.Settings.DefaultLog;}

It is so much simpler in SQL Server 2012 and above, assuming you have default paths set (which is probably always a right thing to do):

select     InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')

Even though this is a very old thread, I feel like I need to contribute a simple solution.Any time that you know where in Management Studio a parameter is located that you want to access for any sort of automated script, the easiest way is to run a quick profiler trace on a standalone test system and capture what Management Studio is doing on the backend.

In this instance, assuming you are interested in finding the default data and log locations you can do the following:

SELECT  SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],  SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog]

I stumbled across this solution in the documentation for the Create Database statement in the help for SQL Server:

SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)                  FROM master.sys.master_files                  WHERE database_id = 1 AND file_id = 1