德瑞克：SQL Server 學習筆記: 交易記錄檔(transaction log)意外損毀，僅剩下資料檔案可用為例。錯誤訊息：訊息 1813，層級 16，狀態 2 ... 檔案啟動錯誤。實體檔案名稱 "*.ldf" 可能不正確。

這裡所指的意外中斷服務情境，例如：
1.系統意外、不正常中斷服務。
2.磁碟損毀。
3.交易記錄檔案(*.ldf)損毀。
等...

發生上述的情境時，若仍是採取一般方式來附加資料庫時，但因為資料庫未正常關閉，導致交易記錄檔案毀損時，將遇到以下的錯誤訊息：

檔案啟動錯誤。實體檔案名稱 "C:\myAdmin\DB\DBLog_log.ldf" 可能不正確。
無法重建記錄，因為關閉資料庫時仍有開啟的交易/使用者、資料庫未發生檢查點，或資料庫是唯讀的。如果手動刪除交易記錄檔，或因硬體或環境失敗而遺失交易記錄檔，就可能發生這種錯誤。
訊息 1813，層級 16，狀態 2，行 1
無法開啟新資料庫 'DBLog'。CREATE DATABASE 已經中止。

情境：
模擬系統損毀，僅剩下資料檔案，沒有交易記錄檔案(*.ldf)。

可能的搶救方式：

適用版本：
SQL Server 2005 與 2008

--步驟01. 請先依據此資料庫的檔案結構，重新建立相同結構、名稱的資料庫。
--步驟02. 正常離線此資料庫，例如執行：
USE master
GO
ALTER DATABASE [資料庫名稱]
SET OFFLINE;
GO

/*=========================================================*/
--步驟03. 將先前的資料檔案覆蓋掉此新建立的資料庫檔案。
--步驟04. 若嘗試執行重新上線此資料庫，例如：
USE master
GO
ALTER DATABASE [資料庫名稱]
SET ONLINE;
GO

/* 遇到的錯誤訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。

訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。

訊息 5069，層級 16，狀態 1，行 1
ALTER DATABASE 陳述式失敗。
*/

--步驟05. 查詢資料庫的狀態為：RECOVERY_PENDING 與 MULTI_USER
/*
RECOVERY PENDING 資料庫狀態
SQL Server 在復原期間發生資源相關的錯誤。
資料庫並未損毀，但是檔案有可能遺失或系統資源限制有可能造成它無法啟動。
資料庫是無法使用的。需要使用者執行其他動作以解決錯誤並讓復原處理得以完成。

MULTI_USER
當指定 MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟06. 先設定此資料庫的狀態為：SINGLE_USER
/*
SINGLE_USER 資料庫狀態
指定每次只能有一位使用者存取資料庫。
當指定 SINGLE_USER 時，每次只有一個使用者可以連接到資料庫。所有其他的使用者連接都會中斷。

如果指定了 SINGLE_USER，且沒有其他使用者連接到資料庫，就會封鎖 ALTER DATABASE 陳述式，直到所有使用者都中斷連接指定的資料庫為止。
若要覆寫這個行為，請參閱 WITH  子句。

資料庫會保留在 SINGLE_USER 模式中，即使是將選項記錄設為關閉的使用者也是如此。此時其他使用者可以連接到這個資料庫，但只能有一位。

將資料庫設為 SINGLE_USER 之前，請先確定 AUTO_UPDATE_STATISTICS_ASYNC 選項是否設為 OFF。
當設為 ON 時，更新統計資料的背景執行緒會取得資料庫連接，而您就無法以單一使用者模式存取資料庫。
若要檢視這個選項的狀態，請查詢 sys.databases 目錄檢視中的 is_auto_update_stats_async_on 資料行。

使用終止選項 WITH ROLLBACK IMMEDIATE。
所有未完成的交易都會回復，而且資料庫的任何其他連接都會立即中斷。
*/
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

/* 遇到的錯誤訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。
訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。
訊息 5069，層級 16，狀態 1，行 1
ALTER DATABASE 陳述式失敗。
*/
-- 查詢資料庫屬性已經變更為：SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟07. 設定資料庫狀態為：EMERGENCY
/*
EMERGENCY  資料庫狀態
使用者已變更資料庫並將狀態設為 EMERGENCY。
資料庫是在單一使用者模式下，而且可以進行修復或還原。

資料庫是標示為 READ_ONLY、記錄已停用並限定只有系統管理員 (sysadmin) 固定伺服器角色的成員才可存取。
EMERGENCY 主要是做為疑難排解的用途。例如，標示為有疑問的資料庫可以設為 EMERGENCY 狀態。

在這個情況下，系統管理員可以進行資料庫的唯讀存取。
只有系統管理員 (sysadmin) 固定伺服器角色的成員，可以將 資料庫設定為 EMERGENCY 狀態。
*/
ALTER DATABASE  [資料庫名稱]
SET EMERGENCY;
GO

-- 查詢資料庫狀態已經變更為：EMERGENCY 與 SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟08. 執行 DBCC CHECKDB 搭配使用 REPAIR_ALLOW_DATA_LOSS 參數。
/*
REPAIR_ALLOW_DATA_LOSS 參數
指定的資料庫必須為單一使用者模式，才能使用。
嘗試修復所有報告的錯誤。這些修復可能會造成某些資料的遺失。

以資料庫緊急模式解決錯誤
如果已經利用 ALTER DATABASE 陳述式將資料庫設為緊急模式，則 DBCC CHECKDB 可以在資料庫上執行某些特殊的修復作業 (如果已指定 REPAIR_ALLOW_DATA_LOSS 選項)。

這些修復可讓一般無法修復的資料庫以實體一致的狀態重新上線。
只有在資料庫無法從備份還原時，才應該使用上述修復來當做最後手段。
*/
DBCC CHECKDB ( [資料庫名稱] , REPAIR_ALLOW_DATA_LOSS) ;
GO

/* 顯示的訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。

警告: 資料庫 'DBLog' 的記錄已經重建。已遺失交易一致性。RESTORE 鏈已中斷，伺服器已經沒有先前記錄檔的內容，因此您必須知道這些內容。
您應該執行 DBCC CHECKDB 來驗證實體一致性。資料庫已進入僅限 DBO 模式。您準備好讓資料庫能供使用後，必須重設資料庫選項，並刪除任何額外的記錄檔。

DBCC 對 'DBLog' 的結果。
...
*/

--步驟09. 正確依序完成步驟08 後，交易記錄檔案已經重新建立完成。

/*=========================================================*/
--步驟10. 查詢資料庫狀態已經變更為：ONLINE 與 SINGLE_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

--步驟11. 再度執行 DBCC CHECKDB 檢查指定資料庫中所有物件的邏輯完整性和實體完整性
use master
GO
DBCC CHECKDB( [資料庫名稱])
GO

/*=========================================================*/
--步驟12. 設定此資料庫的狀態為：MULTI_USER，回復正常上線
/*
當指定 MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO

--步驟13. 查詢資料庫狀態已經變更為：ONLINE 與 MULTI_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
where name='DBLog'
GO