問題描述

我正在嘗試編寫一個自動恢復數據庫備份的腳本.我知道我可以使用以下 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')

這篇關于使用 MOVE 確定 DB RESTORE 的 SQL 數據路徑的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！