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

在 Microsoft SQL Server 之中有好幾個重要的[系統資料庫](https://learn.microsoft.com/en-us/sql/relational-databases/databases/system-databases?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)，其中有個 [`model` 資料庫](https://learn.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 主要用來作為 SQL Server 執行個體上建立之所有新資料庫的範本。因為 SQL Server 每次啟動時，都會重建 `tempdb` 資料庫，因此 `model` 資料庫一定要存在於 SQL Server 系統之中。然而我們前陣子有個客戶，因為 IDC 電源異常導致 `model` 資料庫毀損，進而讓 SQL Server 執行個體完全無法啟動，一卡就是 24 小時才打來求救。老實說這種狀況也不是經常會遇到，今天這篇文章我就來記錄一下重建 `model` 資料庫的過程。

![jandira-sonnendeck-AcW1ZwD-qC0-unsplash2](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/a74c6aa1747df406d42e-210175233-6ee064f6-460c-45e7-8d63-0d17faff585f.jpg)

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

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

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

我們通常[需要備份的系統資料庫](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)僅包含 `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
```

```log
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 組態管理員](https://learn.microsoft.com/zh-tw/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) (SQL Server Configuration Manager) 啟動服務時可能會看到以下錯誤：

![image](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/d54b2ef0cb6a6448a8b8-180603280-79bb0ff2-88fc-4ebf-a643-a7759c5c622c.png)

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

1.  安裝 [SQL Server](https://www.microsoft.com/en-us/sql-server/sql-server-downloads) 任意版本
    
    本篇文章以 SQL Server 2019 Express Edition 為主，但其他版本也都適用。
    
    > 由於 SQL Server LocalDB 並非完整的 SQL Server 版本，本篇文章不適用 LocalDB 喔！
    
    預設安裝路徑: `C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL`
    
2.  安裝 [sqlcmd](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) 工具程式
    
    通常 `sqlcmd` 不需要特別安裝，你只要安裝 [Azure Data Studio](https://learn.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15) 或 [SQL Server Management Studio](https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15) (SSMS) 就會預先安裝 `sqlcmd` 工具。
    
    預設安裝路徑: `C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn`
    
    > 請將此路徑加入 `PATH` 環境變數。
    

### 先搞掛 `model` 資料庫

1.  先用 [SQL Server 組態管理員](https://learn.microsoft.com/zh-tw/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 停用 `SQL Server (SQLEXPRESS)` 執行個體
    
2.  進入 `C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA` 資料夾，並刪除 `model.mdf` 資料檔，或是將 `modellog.ldf` 交易記錄檔弄亂(隨便用 Notepad 開起來亂改存檔即可)，都可以模擬出 `model` 資料庫毀損的狀況。
    
3.  嘗試用 [SQL Server 組態管理員](https://learn.microsoft.com/zh-tw/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 啟動 SQL Server 執行個體，此時你應該會發現已經無法啟動！
    

### 備份 `master` 與 `msdb` 系統資料庫

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

1.  使用 [Trace Flag 3608](https://learn.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#:~:text=%E6%88%96%E5%B7%A5%E4%BD%9C%E9%9A%8E%E6%AE%B5-,3608,-%E9%98%B2%E6%AD%A2SQL%20Server) 追蹤旗標來啟動 SQL Server
    
    使用這個追蹤旗標來啟動 SQL Server 執行個體，可以防止 SQL Server 自動啟動和復原 `master` 資料庫以外的任何資料庫。
    
    ```batch
    cd /d "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn"
    
    "sqlservr.exe" -sSQLEXPRESS -t3608
    ```
    
2.  使用 `sqlcmd` 並使用**信任式連線**連接伺服器
    
    請開啟一個新的 Command Prompt 命令提示字元視窗
    
    ```batch
    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
    
    "SQLCMD.EXE" -S .\SQLEXPRESS -E
    ```
    
3.  備份 `master` 與 `msdb` 系統資料庫
    
    ```sql
    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
    ```
    
    ```log
    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 的安裝檔。你也可以重新[到官網下載](https://www.microsoft.com/en-us/sql-server/sql-server-downloads)安裝檔回來：

-   [SQL Server on-premises](https://download.microsoft.com/download/4/8/6/486005eb-7aa8-4128-aac0-6569782b37b0/SQL2019-SSEI-Eval.exe)
-   [SQL Server 2019 Developer](https://go.microsoft.com/fwlink/p/?linkid=866662)
-   [SQL Server 2019 Express](https://go.microsoft.com/fwlink/p/?linkid=866658)

我以 [SQL Server 2019 Express](https://go.microsoft.com/fwlink/p/?linkid=866658) 為例，下載回來後檔名為 `SQL2019-SSEI-Expr.exe`，執行後會解壓縮出另一個安裝檔 `SQLEXPRADV_x64_ENU.exe`，執行時又會解壓縮到指定資料夾：

![image](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/5af6cb337086ed8e889c-180607136-ea6d81b1-4371-4b9d-aad0-86328c325636.png)

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

![image](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/83c98ccbc17ad4589766-180607207-b3b889f1-751e-47f9-ba69-c90ccab5ba3d.png)

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

```batch
"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](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#Rebuild)

### 還原 master 與 msdb 系統資料庫

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

1.  先用 `single-user mode` 啟動 SQL Server 執行個體
    
    ```batch
    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 執行個體
    
    ```batch
    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
    
    "SQLCMD.EXE" -S .\SQLEXPRESS -E
    ```
    
    你也可以用 `sa` 帳號登入：
    
    ```batch
    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
    
    "SQLCMD.EXE" -S .\SQLEXPRESS -Usa -PtZjFV4Ysv2G
    ```
    
3.  先還原 `master` 資料庫
    
    ```sql
    RESTORE DATABASE master
        FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.bak' WITH REPLACE;
    GO
    ```
    
    以下是執行時的紀錄，重點在倒數第二行：
    
    ```log
    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 組態管理員](https://learn.microsoft.com/zh-tw/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 正常啟動 `SQL Server (SQLEXPRESS)` 服務
    
5.  再次連接 SQL Server 執行個體
    
    ```sql
    cd /d "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
    
    "SQLCMD.EXE" -S .\SQLEXPRESS -E
    ```
    
6.  還原 `msdb` 資料庫
    
    ```sql
    RESTORE DATABASE msdb
        FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\msdb.bak' WITH REPLACE;
    GO
    ```
    
    ```log
    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` 資料庫的修復工作！ 👍

### 相關連結

-   [How to Restore Model Database in SQL Server](https://www.mssqltips.com/sqlservertip/6237/how-to-restore-model-database-in-sql-server/)
-   [model Database - SQL Server | Microsoft Docs](https://learn.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   [SQL Server Model Database Facts](https://www.mssqltips.com/sqlservertip/6786/model-database-sql-server-facts/)
-   備份與還原
    -   [Restore and Recovery Overview (SQL Server)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
    -   [Backup & restore: system databases - SQL Server | Microsoft Docs](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   追蹤旗標 3608
    -   [SQL Server: Trace Flag 3608 - You might encounter this!](https://sqlmaestros.com/sql-server-trace-flag-3608-might-encounter/)
    -   [追蹤旗標 (Transact-SQL) - SQL Server | Microsoft Docs](https://learn.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   命令列參數說明
    -   `setup.exe`: [Install SQL Server from the Command Prompt](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
    -   `sqlservr.exe`: [Database Engine Service Startup Options](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
    -   `sqlcmd.exe`: [sqlcmd Utility](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   可能的錯誤代碼
    -   [MSSQLSERVER\_9004](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-9004-database-engine-error?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) - LOG\_CORRUPT
    -   [MSSQLSERVER\_945](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-945-database-engine-error?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) - DB\_IS\_SHUTDOWN
    -   [Looking to Fix SQL Error 5173?](https://www.sqlserverlogexplorer.com/fix-sql-server-error-5173/)