QA 執行 sp_attach_db 錯誤 1813 解決：離線附加、刪除 LDF、DBCC 修復完整流程

錯誤現象

Msg 1813, Level 16, State 2
Could not open new database 'dbname'.
CREATE DATABASE is terminating abnormally.
Device activation error.
The physical file name 'd:\sql server\mssql\data\dbname_log.ldf' may be incorrect.

原因：僅有 .mdf 檔案，.ldf 遺失/路徑錯誤，SQL Server 無法自動重建記錄檔。

解決方案：標準離線附加流程

1. 建立同名空資料庫（僅建立殼）
2. 停止 SQL Server 服務（服務管理員 / net stop MSSQLSERVER）
3. 用原 .mdf 覆蓋新資料庫的 .mdf
4. 刪除新資料庫的 .ldf（關鍵：讓 SQL Server 強制重建）
5. 啟動 SQL Server 服務
6. 資料庫狀態顯示 疑似 (Suspect) / 單一使用者

緊急修復腳本 (Query Analyzer / SSMS 執行)

USE master;
GO

-- 1. 允許系統表更新 (SQL 2000/2005 相容)
EXEC sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- 2. 設定為緊急模式 (僅 32768)
UPDATE sysdatabases SET status = 32768 WHERE name = '疑似資料庫名';
GO

-- 3. 設定單一使用者模式
EXEC sp_dboption '疑似資料庫名', 'single user', 'true';
GO

-- 4. 執行 DBCC 檢查並修復 (允許資料遺失)
DBCC CHECKDB ('疑似資料庫名', REPAIR_ALLOW_DATA_LOSS);
GO

-- 5. 恢復正常狀態 (28 = 正常)
UPDATE sysdatabases SET status = 28 WHERE name = '疑似資料庫名';
GO

-- 6. 關閉系統表更新
EXEC sp_configure 'allow updates', 0;
RECONFIGURE WITH OVERRIDE;
GO

-- 7. 關閉單一使用者
EXEC sp_dboption '疑似資料庫名', 'single user', 'false';
GO

SQL Server 2005+ 現代寫法 (不需 allow updates)

ALTER DATABASE [疑似資料庫名] SET EMERGENCY;
GO
ALTER DATABASE [疑似資料庫名] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('疑似資料庫名', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE [疑似資料庫名] SET MULTI_USER;
GO
ALTER DATABASE [疑似資料庫名] SET ONLINE;
GO

後續建議

• 修復完成後，**立即完整備份**
• 用腳本產生架構 + BCP/SSIS 導出資料 → **建立全新乾淨資料庫** → 導入資料
• 檢查應用程式完整性，驗證關鍵業務流程
• 根因分析：為何 .ldf 遺失？（磁碟故障、人為刪除、遷移遺漏）

避坑提醒

• REPAIR_ALLOW_DATA_LOSS **會刪除損壞頁面**，可能造成資料遺失，必須在備份後執行
• SQL 2005+ 不支援 直接 UPDATE sysdatabases，必須用 ALTER DATABASE
• sp_attach_single_file_db 僅適用於**乾淨關閉**的資料庫，異常關閉會失敗