SQL Serverインスタンスのデータディレクトリを見つけるにはどうすればよいですか？

質問

私たちは、いくつかの 巨大な データベース (20GB 以上) があり、そのほとんどは静的なルックアップ データを含んでいます。 私たちのアプリケーションはこれらのデータベースのテーブルに対して結合を実行するため、それらは各開発者のローカル SQL サーバーの一部でなければなりません (つまり、中央の共有データベース サーバーでホストすることはできません)。

実際の SQL Server データベース ファイル (*.mdf と *.ldf) の正規セットをコピーして、各開発者のローカル データベースに添付することを計画しています。

ファイルを正しい場所にコピーできるように、ローカルの SQL Server インスタンスのデータ ディレクトリを見つける最良の方法は何でしょうか? これは自動化されたプロセスによって行われるので、ビルド スクリプトからそれを見つけて使用できるようにする必要があります。

どのように解決するのですか?

データファイルとログファイルにデフォルトパスが設定されているかどうかによります。

で明示的にパスが設定されている場合 Properties => Database Settings => Database default locations に格納され、SQL サーバーはそれを Software\Microsoft\MSSQLServer\MSSQLServer に格納されます。 DefaultData と DefaultLog の値として使用することができます。

しかし、これらのパラメータが明示的に設定されていない場合、SQLサーバーはマスターデータベースのデータおよびログのパスを使用します。

下記は、両方のケースをカバーするスクリプトです。これは、SQL Management Studio が実行するクエリの簡略化されたバージョンです。

また、私は xp_instance_regread の代わりに xp_regread というように、このスクリプトはデフォルトでも名前付きでも、どのインスタンスでも動作します。

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

SMOを使用しても同じ結果を得ることができます。以下はC#のサンプルですが、他の.NET言語や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;
}

SQL Server 2012 以降では、デフォルトのパスが設定されていると仮定すると、とてもシンプルになります (これはおそらく、常に正しいことです)。

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