無法啟動 SQL Server：當使用 WITH NORECOVERY 選項還原 model 系統資料庫，資料庫狀態呈現：RESTORING 狀態

有朋友遇到以下的狀況：

將系統資料庫 model，將其資料庫狀態，設定為：RESTORING。
使用版本：SQL Server 2005、SQL Server 2008

進一步討論此狀況，可能會遇到的問題：
若是將系統資料庫 model，將其資料庫狀態，設定為：RESTORING。


將可能造成以下的問題：
停止、再重新啟動 SQL Server，但是系統資料庫 model 卻是 RESTORING 狀態，導致無法啟動 SQL Server。

而且在啟動 SQL Server 時，系統將自動重新建立系統資料庫 tempdb，這時也需要參考到系統資料庫 model。

要能成功啟動 SQL Server 執行個體，三個系統資料庫必須已成功還原。
三個系統資料庫是指：master、model、tempdb 資料庫。

系統資料庫 model 是做為範本用，也會用來建立 tempdb 系統資料庫。

如果無法成功地復原 model 資料庫，會 SQL Server 無法建立 tempdb 資料庫，也不會成功啟動 SQL Server 執行個體。

以下是 SQL Server 錯誤記錄檔的節錄內容：

...
2008-11-23 14:42:47.35 spid9s      Starting up database 'model'.
2008-11-23 14:42:47.39 spid9s      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-11-23 14:42:47.40 spid9s      Error: 927, Severity: 14, State: 2.
2008-11-23 14:42:47.40 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.
2008-11-23 14:42:47.42 spid9s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2008-11-23 14:42:47.42 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
...

...
2008-12-01 12:18:15.34 spid15s     Starting up database 'model'.
2008-12-01 12:18:15.34 spid7s      Server name is 'SQL2K8'. This is an informational message only. No user action is required.
2008-12-01 12:18:15.35 spid15s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-12-01 12:18:15.35 spid15s     錯誤: 927，嚴重性: 14，狀態: 2。
2008-12-01 12:18:15.35 spid15s     資料庫 'model' 無法開啟。它目前正在還原當中。
2008-12-01 12:18:15.35 spid15s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2008-12-01 12:18:15.38 spid17s     錯誤: 9954，嚴重性: 16，狀態: 1。
...

可能的解法：

推想各個資料庫的狀態是寫在系統資料庫 master 內，但是系統資料庫 model 卻是 RESTORING 狀態，導致無法啟動 SQL Server。

目前想到的作法是：(1)

重建 master 資料庫。再執行復原(Restore) master作業。

請參考以下的文件：
重建 master 資料庫的考量http://msdn.microsoft.com/zh-tw/library/ms191431(SQL.90).aspx

(2)
複製另外一台正常的 SQL Server 之 master 資料庫，覆蓋掉受影響的 SQL Server 之 master 資料庫。再執行復原(Restore) master作業。

或許各方高手有更好作法，可以分享一下，在此先謝謝各位的指教與建議。
以下是將系統資料庫 model，設定為 RESTORING 狀態的範例碼，請在測試環境上執行。

-- 以下是對系統資料庫 model 執行完整備份
USE master
GO
BACKUP DATABASE [model]
TO  DISK = N'C:\model.bak'
GO

-- 以下是對系統資料庫 model 執行交易記錄檔備份
/*
搭配 BACKUP LOG 所使用的參數。

參數：NORECOVERY
它會備份記錄的結尾，資料庫會保留在 RESTORING 狀態。當進行容錯移轉，將工作交給次要資料庫時，或在 RESTORE 作業之前儲存記錄結尾時，NORECOVERY 非常有用。

若要執行略過記錄截斷的最大速率記錄備份，再使資料庫自動進入 RESTORING 狀態，請同時使用 NO_TRUNCATE 和 NORECOVERY 選項。

參數：NO_TRUNCATE
它指定不截斷記錄，且使 Database Engine 不論資料庫狀態為何，都一律嘗試進行備份。因此，利用 NO_TRUNCATE 取得的備份可能會有不完整的中繼資料。在資料庫已損毀的情況下，您可以利用這個選項來進行記錄的備份。
*/
BACKUP LOG [model]
TO  DISK = N'C:\model.bak'
WITH  NO_TRUNCATE, NORECOVERY
GO

---

後記：

經過數位同好的建議，筆者這邊又在整理出一份可行的方案，請各位參考：
方法： 利用追蹤旗標 3608 來啟動 SQL Server，可防止復原 master 資料庫以外的資料庫。


步驟如下：

1. 在「命令提示列」視窗中，輸入執行以下命令：

-- 如果是預設 (MSSQLSERVER) 執行個體，請執行下列命令：
NET START MSSQLSERVER /T3608 /c
--如果是具名執行個體，請執行下列命令：
NET START MSSQL$instancename /T3608 /c

/*
啟動參數說明：
T3608：追蹤旗標 3608 可防止 SQL Server 復原 master 資料庫以外的資料庫。
-c：縮短從命令提示字元啟動 SQL Server 的啟動時間。
一般而言，SQL Server Database Engine 會呼叫「服務控制管理員」，以服務方式啟動。因為從命令提示字元啟動時，SQL Server Database Engine 不會以服務方式啟動，所以請使用 -c 略過這個步驟。
*/

請參考下圖所示：



2. 在sqlcmd 的「命令提示列」視窗中，輸入執行以下命令：

-- 使用信任連接登入到預設執行個體
sqlcmd -S localhost -E

3. 在利用 sqlcmd 登入到執行個體後，在「命令提示列」視窗中，輸入執行以下命令：

SELECT name, state_desc FROM sys.databases
GO
/*
我們可以觀察到系統資料庫 model，在 master 上所記錄的狀態是：RESTORING 狀態。這是造成系統無法啟動的主因。
系統資料庫 model 是 RESTORING 狀態，導致無法啟動 SQL Server。
而且在啟動 SQL Server 時，系統將自動重新建立系統資料庫 tempdb，這時也需要參考到系統資料庫 model。
*/

請參考下圖所示：



4. 繼續在 sqlcmd 的「命令提示列」視窗中，輸入執行以下命令：

RESTORE DATABASE model WITH RECOVERY
GO
SELECT name, state_desc FROM sys.databases
GO
-- 檢視系統資料庫 model 是否已經回復到 ONLINE 狀態。

請參考下圖所示：





一般而言，如果遇到一些應該修復系統資料庫的問題時，多半會使用「單一使用者模式」的模式來啟動 SQL Server。


但是在本情境下，可能不適合使用以下的兩個參數：

-f：啟動只含最小組態的 SQL Server 執行個體。
如果組態值設定 (如過度調配記憶體) 造成伺服器無法啟動，這就很有用。
有趣的，使用 -f 啟動參數，系統也將會以「單一使用者模式」來啟動。


-m：當您以單一使用者模式啟動 SQL Server 執行個體。
以單一使用者模式啟動 SQL Server 執行個體時，只有單一使用者可以進行連接，且不會啟動 CHECKPOINT 處理序。


CHECKPOINT 會保證將交易規律地從磁碟快取區寫到資料庫裝置。(一般而言，如果遇到一些應該修復系統資料庫的問題時，就會使用這個選項)。


這個選項會啟用 sp_configure allow updates 選項。根據預設，allow updates 是停用的。


上述的這兩個參數，若是執行 RESTORE DATABASE model WITH RECOVERY ，將產生以下的錯誤。

「訊息 3112，當伺服器處於單一使用者模式時，不得還原 master 資料庫以外的任何資料庫。」

請參考下圖所示：



以下是使用啟動參數：-f 來啟動 SQL Server，以下是 SQL Server 錯誤記錄檔的節錄內容：

...
2008-12-01 14:28:21.94 Server      Command Line Startup Parameters:
/f
2008-12-01 14:28:21.94 Server      Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.  After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
2008-12-01 14:28:21.94 伺服器         SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-12-01 14:28:21.94 伺服器         Detected 4 CPUs. This is an informational message; no user action is required.
2008-12-01 14:28:21.98 伺服器         Using locked pages for buffer pool.
2008-12-01 14:28:21.99 伺服器         Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2008-12-01 14:28:21.99 伺服器         Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2008-12-01 14:28:22.04 伺服器         Node configuration: node 0: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2008-12-01 14:28:22.05 伺服器         Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using the minimal configuration option. This is an informational message only. No user action is required.
2008-12-01 14:28:22.05 spid7s      Warning ******************
2008-12-01 14:28:22.05 spid7s      SQL Server started in single-user mode. This an informational message only. No user action is required.
2008-12-01 14:28:22.05 spid7s      Starting up database 'master'.
...

最後，感謝各位同好提供的建議，也期許能協助各位解決問題，謝謝大家的幫忙...^_^

---

參考文件：
BACKUP (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms186865.aspx

如何使用 SQL Server 中的卸離和附加功能將 SQL Server 資料庫移到新位置
http://support.microsoft.com/kb/224071

使用 SQL Server 服務啟動選項
http://technet.microsoft.com/zh-tw/library/ms190737.aspx

使用 WITH NORECOVERY 選項還原 model 資料庫之後，無法成功啟動 SQL Server 的執行個體
http://support.microsoft.com/kb/822852

FIX： SQL Enterprise Manager RESTORE msdb 離開資料庫載入狀態而不適差異或記錄檔備份
http://support.microsoft.com/kb/319701

移動系統資料庫
http://msdn.microsoft.com/zh-tw/library/ms345408.aspx