如何找到 SQL Server 实例的数据目录? [英] How do I find the data directory for a SQL Server instance?

问题描述

我们有几个巨大数据库(20GB+)，其中大部分包含静态查找数据.因为我们的应用程序对这些数据库中的表执行联接，所以它们必须是每个开发人员本地 SQL Server 的一部分(即它们不能托管在中央共享数据库服务器上).

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).

我们计划复制一组规范的实际 SQL Server 数据库文件(*.mdf 和 *.ldf)，并将它们附加到每个开发人员的本地数据库.

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.

找出本地 SQL Server 实例的数据目录以便我们可以将文件复制到正确位置的最佳方法是什么?这将通过自动化流程完成，因此我必须能够从构建脚本中找到并使用它.

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.

推荐答案

这取决于是否为数据和日志文件设置了默认路径.

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

如果在 Properties => Database Settings => Database default location 中明确设置了路径，则 SQL Server 将其存储在 DefaultData 和 DefaultLog 值中的 Software\Microsoft\MSSQLServer\MSSQLServer.

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.

但是，如果没有明确设置这些参数，SQL Server 将使用 master 数据库的 Data 和 Log 路径.

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

Bellow 是涵盖这两种情况的脚本.这是 SQL Management Studio 运行的查询的简化版本.

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

另外，请注意我使用 xp_instance_regread 而不是 xp_regread，所以这个脚本适用于任何实例，默认或命名.

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

您可以通过使用 SMO 获得相同的结果.Bellow 是 C# 示例，但您可以使用任何其他 .NET 语言或 PowerShell.

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;
}

在 SQL Server 2012 及更高版本中要简单得多，假设您设置了默认路径(这可能总是正确的做法):

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')

这篇关于如何找到 SQL Server 实例的数据目录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！