如何在SQL Server中获得*实际*默认数据库位置?

您有权访问SQL事件探查器吗?

declare @SmoDefaultFile nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
  @SmoDefaultFile OUTPUT

select ISNULL(@SmoDefaultFile,N'') AS [DefaultFile]

编辑:

使用 SYS.DATABASE_FILES :

SELECT df.physical_name
  FROM SYS.DATABASE_FILES df

• 日志=ldf位置,包括ldf文件名

我是这样做的。此示例适用于任何数据库。当您不确定代码中的默认位置时,始终可以创建一个新的临时数据库,并在使用此脚本后删除:

DECLARE @databasetocheck AS nvarchar(128) = 'AnotherTestDb'
SELECT  '[' + @@SERVERNAME + ']' AS [Server Name],
        [sdb].[name] AS [Database Name],
        [saf].[name] AS [Logical File Name],
        [saf].[filename] AS [Full path and name],
        LEFT([saf].[filename], (LEN([saf].[filename]) - LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))) AS [File Location],
        LEFT(RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))), CHARINDEX('.', RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))))-1)AS [Physical File Name],
        RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('.', REVERSE([saf].[filename])) -1))))) AS [File Extension]
FROM    [master].[dbo].[sysaltfiles] [saf], [master].[dbo].[sysdatabases] [sdb]
WHERE   [saf].[dbid] = [sdb].[dbid]
AND     [sdb].[name] = @databasetocheck
GO

-- Find *.MDF and *.LDF database files and corresponding locations for a all databases
SELECT  '[' + @@SERVERNAME + ']' AS [Server Name],
        [sdb].[name] AS [Database Name],
        [saf].[name] AS [Logical File Name],
        [saf].[filename] AS [Full path and name],
        LEFT([saf].[filename], (LEN([saf].[filename]) - LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))) AS [File Location],
        LEFT(RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))), CHARINDEX('.', RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('\', REVERSE([saf].[filename])) -1))))))-1)AS [Physical File Name],
        RIGHT([saf].[filename], (LEN(REVERSE(LEFT(REVERSE([saf].[filename]), CHARINDEX('.', REVERSE([saf].[filename])) -1))))) AS [File Extension]
FROM    [master].[dbo].[sysaltfiles] [saf], [master].[dbo].[sysdatabases] [sdb]
WHERE   ([saf].[fileid] = 1 OR [saf].[fileid] = 2)
AND     [saf].[dbid] = [sdb].[dbid]
GO

祝你好运!

正如OP所指出的 DefaultFile / DefaultLog 不总是存在

通过SMO

考虑到这一点,我总是使用SMO(通过PowerShell)并使用 .DefaultFile .MasterDbPath Server 对象,例如:

$smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $sqlServer
$str = $smoServer.DefaultFile
if ($str)
{
    $str;
}else{
    $smoServer.MasterDBPath;
}

https://gist.github.com/piers7/d8f4a75232e5983f7f5b (完整脚本)

通过注册表

我不认为这是 如果您想直接访问注册表,这很简单,因为我认为masterdb路径只通过服务参数公开,而不是作为专用键公开(可能是错误的:从来没有真正追求过这么多)。

在TSQL中,似乎没有一种特别干净的方法可以做到这一点(例如,使用xp\u instance\u regread需要SA权限,而不仅仅是db\u datacreator—请参阅) https://connect.microsoft.com/SQLServer/feedback/details/515132/management-studio-generates-scripts-that-use-unsupported-undocumented-procedures

CREATE DATABASE blah

…让违约者自己动手!如果您只需要路径,就可以创建数据库 同时设置filesize/growths,这样您就可以 改变 随后数据库将立即执行以下操作:

CREATE DATABASE [$(databaseName)
ALTER DATABASE [$(databaseName)] MODIFY FILE ( NAME = N'$(databaseName)', SIZE = 204800KB )

…根本不用担心路