SQL Server 刪除交易記錄檔與無記錄檔資料庫災難復原實戰

一、安全縮減交易記錄檔（兩種方法）

方法一：GUI 手動操作

1. 資料庫右鍵 → 內容 → 選項 → 復原模式：完整 → 簡單
2. 右鍵 → 工作 → 收縮 → 檔案 → 檔案類型選 記錄 → 收縮至指定大小（如 0 MB）
3. 完成後將復原模式改回 完整，並立即進行完整備份啟動新記錄鏈。

方法二：存儲過程自動化（可排程）

-- 參數：目標記錄檔大小
DECLARE @DBLogSize INT = 0
DECLARE @strDBName NVARCHAR(500), @strLogName NVARCHAR(500), @strSQL VARCHAR(1000)

SELECT @strLogName = B.name, @strDBName = A.name
FROM master.sys.databases A
INNER JOIN sys.master_files B ON A.database_id = B.database_id
WHERE A.database_id = DB_ID()

SET @strSQL = '
ALTER DATABASE [' + @strDBName + '] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (''' + @strLogName + ''' , ' + CONVERT(VARCHAR(20), @DBLogSize) + ');
ALTER DATABASE [' + @strDBName + '] SET RECOVERY FULL'
EXEC(@strSQL)

建立後執行：EXEC usp_ShrinkLog @DBLogSize = 0

二、遺失 .ldf 檔案的資料庫緊急復原

情境：磁碟爆滿導致 .ldf 被誤刪，無法正常附加 .mdf。

步驟

1. 建立同名空資料庫（會產生新 .mdf/.ldf）。
2. 停止 SQL Server 服務。
3. 用原始 .mdf 覆蓋新建的 .mdf，刪除新建的 .ldf。
4. 啟動 SQL Server，資料庫呈現「可疑/無法存取」狀態。
5. 執行緊急修復腳本：

-- 1. 進入緊急模式
ALTER DATABASE [資料庫名稱] SET EMERGENCY
-- 2. 單用戶模式
ALTER DATABASE [資料庫名稱] SET SINGLE_USER
-- 3. 檢查並重建記錄（允許資料遺失）
DBCC CHECKDB('資料庫名稱', REPAIR_ALLOW_DATA_LOSS)
-- 4. 若步驟3報錯，嘗試重建
DBCC CHECKDB('資料庫名稱', REPAIR_REBUILD)
-- 5. 恢復多用戶
ALTER DATABASE [資料庫名稱] SET MULTI_USER

完成後系統自動重建 .ldf，資料庫恢復線上（可能遺失未提交交易）。

替代方案：手動附加重建記錄檔

在 SSMS 附加資料庫對話框中，僅選擇 .mdf，移除 .ldf 行，SQL Server 會嘗試自動重建記錄檔（參考 @碼道程工 方法）。

關鍵提醒

• 平時務必實施定期完整備份 + 記錄備份，才是根本保障。
• 緊急修復屬最後手段，執行前請先備份 .mdf 檔案複本。
• 生產環境嚴禁直接刪除 .ldf 檔案。