如何查找 SQLServer 实例的数据目录？

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

我们计划复制实际 SQLServer 数据库文件的规范集(* 。Mdf 和 * 。) ，并将它们附加到每个开发人员的本地数据库。

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

145233 次浏览
小开

从 GUI: 打开服务器属性，转到 数据库设置，查看 数据库默认位置。

请注意，您可以将数据库文件放在任何您喜欢的地方，尽管将它们保存在默认目录中似乎更为干净。

小开

对于当前数据库，只需使用:

select physical_name fromsys.database_files;

要指定另一个数据库，例如‘ Model’，可以使用 sys.master _ files

select physical_name from sys.master_files where database_id = DB_ID(N'Model');

小开

小问题: 没有数据文件夹，只有一个 违约数据文件夹。

无论如何，要找到它，假设您想安装第一个默认实例:

微软 Microsoft SQL Server MSSQL.1设置 SQLDataRoot

如果有一个命名实例，MSSQL.1就会变成类似 MSSQL10.INSTANCENAME 的东西。

小开

我在 SQLServer 帮助中的创建数据库语句的文档中偶然发现了这个解决方案:

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
小开

SQLServer 的各个组件(Data、 Logs、 SSAS、 SSIS 等)都有一个默认目录。此设置可以在注册表中找到。点击这里阅读更多:

Http://technet.microsoft.com/en-us/library/ms143547%28sql.90%29.aspx

因此，如果您只使用 CREATE DATABASE MyDatabaseName创建一个数据库，那么它将按照上述设置中指定的路径创建。

现在，如果管理员/安装程序更改了默认路径，那么实例的默认路径将存储在注册表中的

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[INSTANCENAME]\Setup

如果您知道实例的名称，则可以查询注册表。这个示例是特定于 SQL2008的-如果您也需要 SQL2005路径，请告诉我。

DECLARE @regvalue varchar(100)


EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Setup',
@value_name='SQLDataRoot',
@value=@regvalue OUTPUT,
@output = 'no_output'


SELECT @regvalue as DataAndLogFilePath

当您使用适当的参数发出 CREATE DATABASE DBName语句时，可以在每个数据库自己的位置创建覆盖服务器设置的每个数据库。可以通过执行 sp _ help db 找到这一点

exec sp_helpdb 'DBName'
小开

可以使用以下 T-SQL 查找当前 SQLServer 实例的默认 Data 和 Log 位置:

DECLARE @defaultDataLocation nvarchar(4000)
DECLARE @defaultLogLocation nvarchar(4000)


EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@defaultDataLocation OUTPUT


EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@defaultLogLocation OUTPUT


SELECT @defaultDataLocation AS 'Default Data Location',
@defaultLogLocation AS 'Default Log Location'
小开

下面是一个完整的脚本，它可以解决这个问题:

@ECHO off
SETLOCAL ENABLEDELAYEDEXPANSION


SET _baseDirQuery=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;
ECHO.
SQLCMD.EXE -b -E -S localhost -d master -Q "%_baseDirQuery%" -W >data_dir.tmp
IF ERRORLEVEL 1 ECHO Error with automatically determining SQL data directory by querying your server&ECHO using Windows authentication.
CALL :getBaseDir data_dir.tmp _baseDir


IF "%_baseDir:~-1%"=="\" SET "_baseDir=%_baseDir:~0,-1%"
DEL /Q data_dir.tmp
echo DataDir: %_baseDir%


GOTO :END
::---------------------------------------------
:: Functions
::---------------------------------------------


:simplePrompt 1-question 2-Return-var 3-default-Val
SET input=%~3
IF "%~3" NEQ "" (
:askAgain
SET /p "input=%~1 [%~3]:"
IF "!input!" EQU "" (
GOTO :askAgain
)
) else (
SET /p "input=%~1 [null]: "
)
SET "%~2=%input%"
EXIT /B 0


:getBaseDir fileName var
FOR /F "tokens=*" %%i IN (%~1) DO (
SET "_line=%%i"
IF "!_line:~0,2!" == "c:" (
SET "_baseDir=!_line!"
EXIT /B 0
)
)
EXIT /B 1


:END
PAUSE
小开
最佳答案

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

如果路径被显式设置为 Properties = > Database Settings = > Database default locations，那么 SQL 服务器将它以 DefaultData和 DefaultLog值存储在 Software\Microsoft\MSSQLServer\MSSQLServer。

但是，如果未显式设置这些参数，则 SQL 服务器将使用主数据库的 Data 和 Log 路径。

下面是涵盖这两种情况的脚本。这是 SQLManagementStudio 运行的查询的简化版本。

另外，请注意，我使用的是 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 来实现相同的结果。Bellow 是 C # sample，但是您可以使用任何其他。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;
}

在 SQLServer2012及以上版本中，假设您已经设置了默认路径(这可能总是正确的做法) ，那么这种方法要简单得多:

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

保持简单:

use master
select DB.name, F.physical_name from sys.databases DB join sys.master_files F on DB.database_id=F.database_id

这将返回所有带有相关文件的数据库

小开

尽管这是一个非常古老的线程，我觉得我需要贡献一个简单的解决方案。 任何时候，只要您知道 Management Studio 中的某个参数位于您想要访问的任何类型的自动化脚本的位置，最简单的方法就是在独立测试系统上运行一个快速分析器跟踪，并捕获 Management Studio 在后端上正在做什么。

在这个例子中，假设您对查找默认数据和日志位置感兴趣，那么您可以执行以下操作:

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

如果用户数据库通过此查询获得默认位置:

declare @DataFileName nVarchar(500)


declare @LogFileName   nVarchar(500)




set @DataFileName = (select top 1 RTRIM(LTRIM(name)) FROM master.sys.master_files where database_id >4 AND file_id = 1)+'.mdf'
set @LogFileName =   (select top 1 RTRIM(LTRIM(name)) FROM master.sys.master_files where database_id >4 AND file_id = 2)+'.ldf'


select
( SELECT top 1 SUBSTRING(physical_name, 1, CHARINDEX(@DataFileName, LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id >4 AND file_id = 1) as 'Data File'
,


(SELECT top 1 SUBSTRING(physical_name, 1, CHARINDEX(@LogFileName, LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id >4 AND file_id = 2)  as 'Log File'
小开

从 SqlServer2012开始，您可以使用以下查询:

SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') as [Default_data_path], SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') as [Default_log_path];

(这是从 http://technet.microsoft.com/en-us/library/ms174396.aspx的一条评论中摘录的，并经过了测试。)

小开

我会做一个简单的备份恢复，因为它更容易和支持版本控制。参考数据尤其需要进行版本控制，以便知道它何时开始生效。一个独立的附属不会给你这个能力。另外，通过备份，您可以继续提供更新后的副本，而不必关闭数据库。

小开

Alex 的回答是正确的，但是对于子孙后代来说，还有另一个选择: 创建一个新的空数据库。如果您使用 CREATE DATABASE 而没有指定目标目录，那么您将得到... 默认的 data/log 目录。放松。

不过就我个人而言，我可能会选择:

  • 将数据库恢复到开发人员的 PC，而不是复制/附加(备份可以压缩，在 UNC 上公开) ，或者
  • 使用链接服务器首先避免这样做(取决于有多少数据通过连接)

即使在2015年，20gb 也不是很大，但这都是相对的。

小开 
SELECT DISTINCT dbo.GetDirectoryPath(filename) AS InstanceDataPaths
FROM sys.sysaltfiles WHERE filename like '%.mdf' and filename not like '%\MSSQL\Binn\%'


SELECT DISTINCT dbo.GetDirectoryPath(filename) AS InstanceLogPaths
FROM sys.sysaltfiles WHERE filename like '%.ldf' and filename not like '%\MSSQL\Binn\%'

enter image description here

您可以从 如何查找 SQLServer 实例的数据目录下载详细的 SQL 脚本


提交答案