僅有 MDF 檔案時如何恢復 SQL Server 資料庫

適用情境

僅擁有資料檔（.mdf），遺失記錄檔（.ldf），需快速掛載資料庫。常見於：備份遺漏記錄檔、記錄檔損壞無法修復、跨環境遷移僅複製 MDF。

操作步驟

1. 建立同名空資料庫（檔案路徑可自訂）：

   CREATE DATABASE [Test] ON PRIMARY 
   (NAME = N'Test', FILENAME = N'D:\Data\Test.mdf', SIZE = 10MB, FILEGROWTH = 10MB)
   LOG ON 
   (NAME = N'Test_log', FILENAME = N'D:\Log\Test_log.ldf', SIZE = 5MB, FILEGROWTH = 5MB);

2. 將資料庫設為離線：

   ALTER DATABASE [Test] SET OFFLINE WITH ROLLBACK IMMEDIATE;

3. 刪除剛建立的記錄檔（.ldf）——此步驟關鍵，保留會導致後續掛載失敗。
4. 用單一檔案掛載資料庫（需 sysadmin 權限）：

   EXEC sp_attach_single_file_db @dbname = 'Test', 
       @physname = 'D:\Data\Test.mdf';

   SQL Server 會自動在資料檔同目錄下建立新的記錄檔（約 1-2 MB），資料庫進入復原模式並完成啟動。

5. 設為線上並驗證：

   ALTER DATABASE [Test] SET ONLINE;
   SELECT * FROM [Test].sys.objects WHERE type = 'U'; -- 確認表格存在

注意事項與限制

• 資料庫版本相容性：MDF 來源版本必須 ≤ 目標執行個體版本（不支援降級掛載）。
• 未提交交易將丟失：重建記錄檔相當於截斷記錄，所有未認可的交易回滾，資料庫呈現交易一致狀態。
• 若資料庫原為複寫/鏡像/Always On 成員，需先移除相關設定再操作。
• SQL Server 2016+ 建議改用 CREATE DATABASE ... FOR ATTACH_REBUILD_LOG，語法更清晰且支援指定新記錄檔路徑。

替代方案比較

最佳實務建議

1. 平時備份務必包含 完整備份 + 記錄備份，避免單點故障。
2. 定期演練「僅有 MDF」恢復流程，驗證 RTO。
3. 生產環境嚴禁直接刪除 .ldf；如記錄檔異常，優先嘗試 DBCC CHECKDB 或還原備份。