重建 SQL Server master 資料庫實戰 (一)：完整前置準備與腳本產生

因應叢集擴充需變更伺服器定序，必須重建 master 資料庫。重建相當於從安裝媒體重新部署系統資料庫，所有登入、作業、連結伺服器等中繼資料將遺失。本文詳細說明重建前的完整準備工作，包含分離資料庫、產生還原腳本，確保重建後能快速恢復原狀。

核心觀念

• 重建 master = 重新安裝系統資料庫，所有系統層級物件歸零
• 「磨刀不誤砍柴功」：完整準備可將重建風險降至最低
• 所有查詢結果建議以「文字輸出」(Ctrl+T) 存檔，便於重建後執行

準備工作清單與腳本

任務 1：分離所有使用者資料庫 → 產生 0.sql

重建前必須分離所有使用者資料庫，保護資料檔案 (.mdf/.ldf) 不受影響。

DECLARE @Command_Detach VARCHAR(1000)
SELECT @Command_Detach = '
IF N''[?]'' NOT IN(''model'',''master'',''msdb'',''tempdb'')
BEGIN
    SELECT ''EXEC usp_KillDBConnections ''''?'''''
    EXEC sp_detach_db ''''?'''',''''true''''
END'
EXEC sp_MSforeachdb @Command_Detach

輸出結果存為 0.sql，重建前執行。

任務 2：附加所有使用者資料庫 → 產生 1.sql

重建後需重新附加資料庫。注意附加後擁有者可能變為 dbo，需同步處理擁有權變更。

DECLARE @Command_Attach VARCHAR(1000)
SELECT @Command_Attach = '
IF N''[?]'' NOT IN(''model'',''master'',''msdb'',''tempdb'')
BEGIN
    SELECT ''EXEC sp_attach_db ''''?'''',''
    '''''' + RTRIM(filename) + '''''',''
    FROM [?]..sysfiles
    WHERE fileid = (SELECT MIN(fileid) FROM [?]..sysfiles)
    UNION ALL
    SELECT '''''''' + RTRIM(filename) + ''''''''
    FROM [?]..sysfiles
    WHERE fileid > (SELECT MIN(fileid) FROM [?]..sysfiles) AND
          fileid < (SELECT MAX(fileid) FROM [?]..sysfiles)
    UNION ALL
    SELECT '''''''' + RTRIM(filename) + ''''''''
    FROM [?]..sysfiles
    WHERE fileid = (SELECT MAX(fileid) FROM [?]..sysfiles)
END'
EXEC sp_msforeachdb @Command_Attach

輸出結果存為 1.sql，重建後執行。

任務 3：備份所有登入 → 產生 2.sql

登入資訊儲存於 master，重建後遺失。使用 Microsoft 官方腳本 sp_help_revlogin (KB918992) 產生重建登入的 T-SQL。

1. 下載並執行 KB918992 腳本，建立 sp_hexadecimal、sp_help_revlogin 兩個預存程序
2. 執行 EXEC dbo.sp_help_revlogin
3. 將輸出結果存為 2.sql

任務 4：處理資料庫 dbo 擁有者映射 → 產生 3.sql

附加資料庫後，dbo 對應的登入可能不正確，需產生 sp_changedbowner 腳本修正。

DECLARE @Command_dbo VARCHAR(1000)
SELECT @Command_dbo = '
use [?]
DECLARE @Owners TABLE (dbid int, dbname varchar(100), loginname varchar(100))
INSERT INTO @Owners (dbid, dbname, loginname)
SELECT DB_ID(), DB_NAME(), SL.[name]
FROM sysusers SU LEFT JOIN master..syslogins SL ON SU.sid = SL.sid
WHERE SU.[name] = ''dbo''
SELECT ''EXEC [?].dbo.sp_changedbowner N'''''' + loginname + '''''', '' + ''false''
FROM @Owners WHERE dbid = DB_ID()'
EXEC sys.sp_MSforeachdb @Command_dbo

輸出結果存為 3.sql，重建後執行。

任務 5：備份系統資料庫 Securables → 產生 4.sql

系統資料庫 (master、msdb、model) 的權限設定重建後遺失。使用 Pedro Lopes 提供的腳本產生權限重建 T-SQL，僅針對系統資料庫 (使用者資料庫隨附加自動保留)。

輸出結果存為 4.sql，重建後執行。

任務 6：備份 master/msdb 中自訂物件

如自建的預存程序、函數 (sp_hexadecimal、sp_help_revlogin 等)，重建後需重新建立。使用 SSMS 物件總管 → 右鍵 → Script Object As → CREATE To → File，逐一存檔。

任務 7：備份其他系統層級物件

• 系統物件 (System Objects)
• SQL Agent Jobs、Operators、Alerts
• 連結伺服器、備份裝置、維護計畫等

同樣使用 SSMS 產生 CREATE 腳本存檔，重建後依序執行。

總結

完成上述 7 項準備，即擁有重建 master 後全量恢復執行個體所需的所有腳本。這套流程不僅適用於定序變更，也是災難恢復 (DR) 的標準作業程序。下一篇將實際演示重建執行與恢復流程。