The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何修復 Microsoft SQL Server 毀損的 model 資料庫

📅 2023/01/01 23:07 📁 SQL Server

在 Microsoft SQL Server 之中有好幾個重要的系統資料庫，其中有個 model 資料庫 主要用來作為 SQL Server 執行個體上建立之所有新資料庫的範本。因為 SQL Server 每次啟動時，都會重建 tempdb 資料庫，因此 model 資料庫一定要存在於 SQL Server 系統之中。然而我們前陣子有個客戶，因為 IDC 電源異常導致 model 資料庫毀損，進而讓 SQL Server 執行個體完全無法啟動，一卡就是 24 小時才打來求救。老實說這種狀況也不是經常會遇到，今天這篇文章我就來記錄一下重建 model 資料庫的過程。

jandira-sonnendeck-AcW1ZwD-qC0-unsplash2

注意: 本篇文章的前提是 master 資料庫並沒有毀損！

SQL Server 的系統資料庫主要有以下四個：

  1. master 用來保存所有執行個體層級的資訊
  2. msdb 用來保存所有與 SQL Server Agent 相關的資訊
  3. model 用來當成所有新資料庫的範本
  4. tempdb 用來保存 SQL Server 執行過程中的暫存資料 (每次啟動執行個體都會重建)

我們通常需要備份的系統資料庫僅包含 master, msdb 與 model 資料庫。一般來說我們都不太會更新 model 資料庫的內容，所以通常在異動之後做一次完整備份即可，沒異動過的話，其實也不太需要備份，壞掉的時候重建即可。

由於 model 資料庫非常重要，而且每次啟動 SQL Server 執行個體都需要透過 model 資料庫建立 tempdb 資料庫。因此，當 model 資料庫毀損的時候，SQL Server 執行個體是無法順利啟動的，你可以從 SQL Server 啟動記錄檔中看到以下錯誤：

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\ERRORLOG

2022-07-23 21:07:10.72 spid14s     Starting up database 'model'.
2022-07-23 21:07:10.74 spid14s     Error: 5173, Severity: 16, State: 1.
2022-07-23 21:07:10.74 spid14s     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
2022-07-23 21:07:10.74 spid14s     Error: 5173, Severity: 16, State: 2.
2022-07-23 21:07:10.74 spid14s     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
2022-07-23 21:07:10.74 spid14s     Log file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\model_log.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
2022-07-23 21:07:10.74 spid14s     Error: 945, Severity: 14, State: 2.
2022-07-23 21:07:10.74 spid14s     Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2022-07-23 21:07:10.74 spid14s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

若使用 SQL Server 組態管理員 (SQL Server Configuration Manager) 啟動服務時可能會看到以下錯誤：

image

完成本文實作所需的必備條件 (Prerequisites)

  1. 安裝 SQL Server 任意版本

    本篇文章以 SQL Server 2019 Express Edition 為主，但其他版本也都適用。

    由於 SQL Server LocalDB 並非完整的 SQL Server 版本，本篇文章不適用 LocalDB 喔！

    預設安裝路徑: C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL

  2. 安裝 sqlcmd 工具程式

    通常 sqlcmd 不需要特別安裝，你只要安裝 Azure Data Studio 或 SQL Server Management Studio (SSMS) 就會預先安裝 sqlcmd 工具。

    預設安裝路徑: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn

    請將此路徑加入 PATH 環境變數。

先搞掛 model 資料庫

  1. 先用 SQL Server 組態管理員 停用 SQL Server (SQLEXPRESS) 執行個體

  2. 進入 C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA 資料夾，並刪除 model.mdf 資料檔，或是將 modellog.ldf 交易記錄檔弄亂(隨便用 Notepad 開起來亂改存檔即可)，都可以模擬出 model 資料庫毀損的狀況。

  3. 嘗試用 SQL Server 組態管理員 啟動 SQL Server 執行個體，此時你應該會發現已經無法啟動！

備份 master 與 msdb 系統資料庫

由於 SQL Server 執行個體目前是無法順利啟動的狀態，我們必須先讓 SQL Server 執行個體啟動，才能備份目前的 master 與 msdb 資料庫。

  1. 使用 Trace Flag 3608 追蹤旗標來啟動 SQL Server

    使用這個追蹤旗標來啟動 SQL Server 執行個體，可以防止 SQL Server 自動啟動和復原 master 資料庫以外的任何資料庫。

    cd /d "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn"

"sqlservr.exe" -sSQLEXPRESS -t3608

  2. 使用 sqlcmd 並使用信任式連線連接伺服器

    請開啟一個新的 Command Prompt 命令提示字元視窗

    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"

"SQLCMD.EXE" -S .\SQLEXPRESS -E

  3. 備份 master 與 msdb 系統資料庫

    BACKUP DATABASE master
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.bak';
GO

BACKUP DATABASE msdb
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\msdb.bak';
GO

    1> BACKUP DATABASE master
2>     TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.bak';
3> GO
Processed 520 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 522 pages in 0.015 seconds (271.614 MB/sec).
1>
2> BACKUP DATABASE msdb
3>     TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\msdb.bak';
4> GO
Processed 2184 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 2 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 2186 pages in 0.033 seconds (517.400 MB/sec).

  4. 回到原本執行 sqlservr.exe 的命令提示字元視窗，按下 Ctrl+C 並按下 Y 關閉 SQL Server 執行個體

重建 SQL Server 系統資料庫

重建 SQL Server 系統資料庫，必須先取得當初安裝 SQL Server 的安裝檔。你也可以重新到官網下載安裝檔回來：

我以 SQL Server 2019 Express 為例，下載回來後檔名為 SQL2019-SSEI-Expr.exe，執行後會解壓縮出另一個安裝檔 SQLEXPRADV_x64_ENU.exe，執行時又會解壓縮到指定資料夾：

image

此時你就可以在解壓縮目錄中找到 SETUP.EXE 安裝程式。

image

接著請執行以下命令，大約等個兩三分鐘，就可以順利重建系統資料庫：

"C:\SQL2019\SETUP.EXE" /Q /ACTION=Rebuilddatabase /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=tZjFV4Ysv2G

參數說明：

  • /Q 安靜模式 (注意: 如果在 1 分鐘內執行完畢，那應該是沒有正確的重建資料庫！)
  • /ACTION=Rebuilddatabase 重建系統資料庫
  • /INSTANCENAME=SQLEXPRESS 指定執行個體名稱為 SQLEXPRESS
  • /SQLSYSADMINACCOUNTS=BUILTIN\Administrators 指定本機管理者群組(BUILTIN\Administrators)預設擁有 sysadmin 伺服器角色
  • /SAPWD=tZjFV4Ysv2G 指定預設 sa 帳戶密碼為 tZjFV4Ysv2G

完整說明參見 Install SQL Server from the Command Prompt

還原 master 與 msdb 系統資料庫

在重建系統資料庫之後，我們要先還原之前的 master 與 msdb 系統資料庫，確保我們的資料庫可以還原到原本的的狀態。

  1. 先用 single-user mode 啟動 SQL Server 執行個體

    cd /d "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn"

"sqlservr.exe" -sSQLEXPRESS -mSQLCMD

    參數說明：

    • -sSQLEXPRESS 啟動名為 SQLEXPRESS 的執行個體
    • -mSQLCMD 其中 -m 代表使用 single-user mode 啟動 SQL Server 執行個體，而 SQLCMD 則代表只有 SQL Client 的 Application Name 必須為 SQLCMD 才能連接 SQL Server 執行個體

    由於目前的 master 資料庫是重建後的版本，因此不會有任何使用者資料庫被自動掛載上來。

  2. 使用 SQLCMD.exe 連結 SQL Server 執行個體

    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"

"SQLCMD.EXE" -S .\SQLEXPRESS -E

    你也可以用 sa 帳號登入：

    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"

"SQLCMD.EXE" -S .\SQLEXPRESS -Usa -PtZjFV4Ysv2G

  3. 先還原 master 資料庫

    RESTORE DATABASE master
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.bak' WITH REPLACE;
GO

    以下是執行時的紀錄，重點在倒數第二行：

    1> RESTORE DATABASE master
2>     FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.bak' WITH REPLACE;
3> GO
Processed 520 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

    注意: 還原 master 資料庫的過程會讓 SQL Server 執行個體自動關閉！

  4. 請用 SQL Server 組態管理員 正常啟動 SQL Server (SQLEXPRESS) 服務

  5. 再次連接 SQL Server 執行個體

    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"

"SQLCMD.EXE" -S .\SQLEXPRESS -E

  6. 還原 msdb 資料庫

    RESTORE DATABASE msdb
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\msdb.bak' WITH REPLACE;
GO

    1> RESTORE DATABASE msdb
2>     FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\msdb.bak' WITH REPLACE;
3> GO
Processed 2184 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 2 pages for database 'msdb', file 'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 2186 pages in 0.032 seconds (533.569 MB/sec).

恭喜你，你已經完成 model 資料庫的修復工作！ 👍

相關連結

留言評論