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

Question

We have a few huge databases (20GB+) which mostly contain static lookup data. Because our application executes joins against tables in these databases, they have to be part of each developers local SQL Server (i.e. they can't be hosted on a central, shared database server).

We plan on copying a canonical set of the actual SQL Server database files (*.mdf and *.ldf) and attach them to each developer's local database.

What's the best way to find out the local SQL Server instance's data directory so we can copy the files to the right place? This will be done via an automated process, so I have to be able to find and use it from a build script.

Answer 1

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 output

declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output

declare @DefaultBackup nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output

declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @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 output
select @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')