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
參考資料:
自動重建交易記錄檔案(*.ldf),以正常卸離情況為例
http://sharedderrick.blogspot.com/2010/02/ldf.html
資料庫狀態
http://msdn.microsoft.com/zh-tw/library/ms190442.aspx
DBCC CHECKDB (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176064.aspx
INF:有修復選項的 DBCC CHECKDB/CHECKTABLE 要求資料庫必須為「單一使用者模式」
http://support.microsoft.com/kb/264154/zh-tw
In Recovery... CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx
When should you rebuild the transaction log?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx
Which part of REPAIR_ALLOW_DATA_LOSS isnt clear?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx
Ta da! Emergency mode repair
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx
attaching DB without .ldf file ??
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54
這篇太重要了...幾乎救了我一命
回覆刪除也提醒您,記得定期備份資料庫
回覆刪除