問題描述

我正在嘗試編寫一個(gè)自動(dòng)恢復(fù)數(shù)據(jù)庫備份的腳本.我知道我可以使用以下 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

這樣做的問題是我希望能夠在運(yùn)行時(shí)確定 SQL 服務(wù)器的數(shù)據(jù)位置(即 TO 路徑)，以便恢復(fù)的數(shù)據(jù)庫與該服務(wù)器上的其他數(shù)據(jù)庫保持一致.

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.

要恢復(fù)的數(shù)據(jù)庫在要恢復(fù)到的服務(wù)器上不存在，我需要 MOVE 語句，因?yàn)樵捶?wù)器可能是 SQL Server 2005，目標(biāo)是 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 數(shù)據(jù)位置?

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

推薦答案

我發(fā)現(xiàn)的唯一可行的解??決方案是從您的 T-SQL 代碼中檢查注冊(cè)表:

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'

我可以發(fā)誓數(shù)據(jù)路徑將存儲(chǔ)在 SERVERPROPERTY 或動(dòng)態(tài)管理視圖 (DMV) 中的某處 - 但沒有運(yùn)氣......

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

這篇關(guān)于使用 MOVE 確定 DB RESTORE 的 SQL 數(shù)據(jù)路徑的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！