使用 MOVE 确定 DB RESTORE 的 SQL 数据路径

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试编写一个自动恢复数据库备份的脚本.我知道我可以使用以下 RESTORE 命令:

I'm trying to write a script which automatically restores a database backup. I know I can use the following RESTORE command:

RESTORE DATABASE [DBRestoredName] 
FROM  DISK = N'C:\path\to\backup.bak' 
WITH  FILE = 1,  
MOVE N'DBNAME' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.mdf',  
MOVE N'DBNAME_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.ldf',  
NOUNLOAD

这样做的问题是我希望能够在运行时确定 SQL 服务器的数据位置(即 TO 路径)，以便恢复的数据库与该服务器上的其他数据库保持一致.

The problem with this is I want to be able to determine the SQL server's data location (i.e. the TO path) at run-time so the restored database is placed consistently alongside other databases on this server.

要恢复的数据库在要恢复到的服务器上不存在，我需要 MOVE 语句，因为源服务器可能是 SQL Server 2005，目标是 2008，因此备份文件中包含的文件路径是不可取.

The database being restored won't exist on the server it's being restored to and I require the MOVE statements as the source server is likely to be SQL server 2005 and the target is 2008 therefore the file paths included in the backup file are not desirable.

那么我可以通过哪些方式以编程方式确定 SQL 数据位置?

So what ways could I determine the SQL data location programmatically?

推荐答案

我发现的唯一可行的解​​决方案是从您的 T-SQL 代码中检查注册表:

The only viable solution I found is inspecting the registry from your T-SQL code:

DECLARE @filepath NVARCHAR(260)

EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
        N'DefaultData', 
        @filepath output, 'no_output' 

SELECT @filepath as 'Your default data directory'

我可以发誓数据路径将存储在 SERVERPROPERTY 或动态管理视图 (DMV) 中的某处 - 但没有运气......

I could have sworn that data path would be stored somewhere in a SERVERPROPERTY or a Dynamic Management View (DMV) - but no luck ......

更新:正如@Mike 指出的 - 在 SQL Server 2012 和更新版本中，该信息可作为 SERVERPROPERTY 使用:

Update: as @Mike pointed out - in SQL Server 2012 and newer, that information is available as a SERVERPROPERTY:

SELECT 
    DefaultDataPath = SERVERPROPERTY('InstanceDefaultDataPath'),
    DefaultLogPath = SERVERPROPERTY('InstanceDefaultLogPath')

相关文章