如何找到 SQL Server 实例的数据目录？

Question

提问by Aaron Jensen

We have a few hugedatabases (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).

我们有几个大型数据库（20GB+），其中大部分包含静态查找数据。因为我们的应用程序对这些数据库中的表执行联接，所以它们必须是每个开发人员本地 SQL 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.

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

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.

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

Answer 1

回答by Alex Aza

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 locationsthen SQL server stores it at Software\Microsoft\MSSQLServer\MSSQLServerin DefaultDataand DefaultLogvalues.

如果路径在Properties=> Database Settings=>处显式设置，Database default locations则 SQL Server 将其存储在Software\Microsoft\MSSQLServer\MSSQLServerin DefaultData和DefaultLogvalues 处。

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

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

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

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

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

另外，请注意，我使用的是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

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

您可以通过使用 SMO 获得相同的结果。Bellow 是 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;
}

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

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

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

Answer 2

回答by TDrudge

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.

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

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]

Answer 3

回答by Aaron Jensen

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

我在 SQL Server 帮助中的 Create Database 语句的文档中偶然发现了这个解决方案：

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

Answer 4

回答by Remus Rusanu

For the current database you can just use:

对于当前数据库，您可以只使用：

select physical_name fromsys.database_files;

to specify another database e.g. 'Model', use sys.master_files

要指定另一个数据库，例如“模型”，请使用 sys.master_files

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

Answer 5

回答by Nathan

As of Sql Server 2012, you can use the following query:

从 Sql Server 2012 开始，您可以使用以下查询：

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

(This was taken from a comment at http://technet.microsoft.com/en-us/library/ms174396.aspx, and tested.)

（这取自http://technet.microsoft.com/en-us/library/ms174396.aspx的评论，并经过测试。）

Answer 6

回答by Raj More

Various components of SQL Server (Data, Logs, SSAS, SSIS, etc) have a default directory. The setting for this can be found in the registry. Read more here:

SQL Server 的各种组件（数据、日志、SSAS、SSIS 等）都有一个默认目录。可以在注册表中找到此设置。在此处阅读更多信息：

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

So if you created a database using just CREATE DATABASE MyDatabaseNameit would be created at the path specified in one of the settings above.

因此，如果您仅使用CREATE DATABASE MyDatabaseName它创建了一个数据库，它将在上述设置之一中指定的路径中创建。

Now, if the admin / installer changed the default path, then the default path for the instance is stored in the registry at

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

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

If you know the name of the instance then you can query the registry. This example is SQL 2008 specific - let me know if you need the SQL2005 path as well.

如果您知道实例的名称，则可以查询注册表。此示例特定于 SQL 2008 - 如果您还需要 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

Each database can be created overriding the server setting in a it's own location when you issue the CREATE DATABASE DBNamestatement with the appropriate parameters. You can find that out by executing sp_helpdb

当您CREATE DATABASE DBName使用适当的参数发出语句时，可以创建每个数据库以覆盖其自己位置中的服务器设置。您可以通过执行 sp_helpdb 找到它

exec sp_helpdb 'DBName'

Answer 7

回答by Deptor

Keeping it simple:

保持简单：

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

this will return all databases with associated files

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

Answer 8

回答by Michael Petrotta

From the GUI: open your server properties, go to Database Settings, and see Database default locations.

从 GUI：打开您的服务器属性，转到Database Settings，然后查看Database default location。

Note that you can drop your database files wherever you like, though it seems cleaner to keep them in the default directory.

请注意，您可以将数据库文件放置在任何您喜欢的位置，但将它们保存在默认目录中似乎更简洁。

Answer 9

回答by Regent

You can find default Data and Log locations for the current SQL Server instance by using the following T-SQL:

您可以使用以下 T-SQL 查找当前 SQL Server 实例的默认数据和日志位置：

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'

Answer 10

回答by djangofan

Expanding on "splattered bits" answer, here is a complete script that does it:

扩展“飞溅的位”答案，这是一个完整的脚本：

@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

如何找到 SQL Server 实例的数据目录？

提问by Aaron Jensen

回答by Alex Aza

回答by TDrudge

回答by Aaron Jensen

回答by Remus Rusanu

回答by Nathan

回答by Raj More

回答by Deptor

回答by Michael Petrotta

回答by Regent

回答by djangofan

相关推荐

最近更新

标签

如何找到 SQL Server 实例的数据目录？

提问by Aaron Jensen

回答by Alex Aza

回答by TDrudge

回答by Aaron Jensen

回答by Remus Rusanu

回答by Nathan

回答by Raj More

回答by Deptor

回答by Michael Petrotta

回答by Regent

回答by djangofan

相关推荐

SQL select 语句字符串连接

SQL 如何聚合布尔列

SQL 如何从sql server master数据库中查找任何数据库中任何表的列数？

如何解决 SQL Server 错误“在预期条件的上下文中指定的非布尔类型的表达式，靠近‘GROUP’”

相关推荐

最近更新

标签