積沙成塔

【情境說明】

前幾天 Sky 突然出考題問筆者他在 SQL Server 2012 Express LocalDB（以下簡稱 LocalDB）中嘗試還原資料庫，會發生【設定 'Microsoft.SqlServer.Management.Smo.Settings' 無法使用屬性 BackupDirectory。此物件可能沒有此屬性，或因為存取權限不足而無法擷取。 (Microsoft.SqlServer.Smo)】的錯誤訊息，經過一番討論後，Sky 最後是使用 T-SQL 來還原資料庫到 LocalDB（詳見LocalDB - 在LocalDB上使用SSMS還原資料庫產生錯誤），後來在保哥 BLOG 看到 如何還原資料庫到 SQL Server 2012 LocalDB 執行個體 一文，裡面提到發生這個問題的原因以及可以利用建立機碼的方式來強制設定 LocalDB 的資料庫預設位置，使得 LocalDB 中可以順利還原資料庫。

當您使用 SSMS 來修改 SQL Server 的伺服器驗證模式（如下圖），其實背後是使用 xp_instance_regwrite 擴充預存程序來修改機碼，以達到變更驗證模式的目的。

 

 

產生的指令碼如下：

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer'

        , N'LoginMode'

        , REG_DWORD

        , 1

GO

經過上述的例子，筆者聯想到或許也可以利用擴充預存程序來設定  LocalDB 的資料庫預設路徑，但特別強調這樣的做法是基於實驗性質，但實務上比較不建議這樣做，本文的重點在於介紹其實 SQL Server 中也有這樣的功能可以維護機碼。

【使用 xp_regwrite 寫入機碼】

使用 xp_regwrite 擴充預存程序來建立 LocalDB 執行個體的資料庫預設位置，分別將資料庫路徑、交易紀錄檔路徑以及備份路徑都設定到 C:\Temp，指令碼如下：

USE [master]

GO

--資料檔路徑

EXEC xp_regwrite @rootkey=N'HKEY_LOCAL_MACHINE'

                , @key=N'Software\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer'

                , @value=N'DefaultData'

                , @type='REG_SZ'

                , @value= N'C:\Temp\'

GO

--交易紀錄檔路徑

EXEC xp_regwrite @rootkey=N'HKEY_LOCAL_MACHINE'

                , @key=N'Software\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer'

                , @value=N'DefaultLog'

                , @type='REG_SZ'

                , @value= N'C:\Temp\'

GO

--備份路徑

EXEC xp_regwrite @rootkey=N'HKEY_LOCAL_MACHINE'

                , @key=N'Software\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer'

                , @value=N'BackupDirectory'

                , @type='REG_SZ'

                , @value= N'C:\Temp\'

GO

若您執行上述指令碼時發生如下圖的錯誤時，是由於啟動 LocalDB 執行個體的帳戶權限不足所致，筆者的解決方式是切換至預設執行個體後再來執行上述的指令碼。

說明：由於筆者的測試環境中預設執行個體使用 SYSTEM 帳號來啟動 SQL Server 服務，所以有權限存取機碼，實務上請依最小權限的服務帳戶原則來啟動 SQL Server。