SQL Server 最佳案例 - 如何修復一個有問題的資料庫 (Suspect Database)

本篇文章是從國外的 SQL Skill.com 的網站中轉載而來，主要說明如何修復並重新附加上一個有問題的資料庫，並說明其操作步驟，在請參考。

原文主旨：Creating, detaching, re-attaching, and fixing a suspect database

前幾年作者開始寫部落格時，嘗試去處理一些問題，其中如何去重新附加(Attach)一個有問題(Suspect)的資料庫，這是作者在論壇上看到的一個有趣情境 - DBA嘗試著去附加與卸除(attach/detach)一個有問題的資料庫，但是過程中發生錯誤，於是作者在年度TechEd的議程上寫了一個範例顯示如何建立一個有問題的資料庫與透過 hex editor 顯示和卸除後並重新附加和修正。這篇雖然已經在議程上過了很久，但是作者由於一直很忙，所以透過空餘的時間終於完成，並發佈在部落格上。


1、產生一個有問題的資料庫
開始時我先來產生一個簡單的資料庫，稱為 DemoSuspect 其中包含一個表格和一些隨機的資料。

USE MASTER
GO
CREATE DATABASE DemoSuspect;
GO
USE DemoSuspect;
GO
CREATE TABLE Employees (FirstName VARCHAR (20), LastName VARCHAR (20), YearlyBonus INT);
GO
INSERT INTO Employees VALUES ('Paul', 'Randal', 10000);
INSERT INTO Employees VALUES ('Kimberly', 'Tripp', 10000);
GO

現在我將啟用一個交易並保持在更新狀態中，強制他寫入到磁碟與建立一個 CheckPoint。並且將Kimberly's的Bouns設定為0。


-- Simulate an in-flight transaction
BEGIN TRAN;
UPDATE Employees SET YearlyBonus = 0
WHERE LastName = 'Tripp';GO
CHECKPOINT;GO

然後在另外一個視窗，我將模擬一個損壞的情況，使用下列的指令：


SHUTDOWN WITH NOWAIT;GO

現在 SQL Server 已經關機了，我模擬一個I/O的錯誤並且明確的呈現在紀錄檔中。我嘗試使用一個hex的編輯去編輯他，在編輯器上選擇一個直接有用的XVI32工具。我打開紀錄檔後，將開始的區段填入0，並且儲存，螢幕顯示如下。

當我再次啟動 SQL Server，他將嘗試去執行復原DemoSuspect資料庫但失敗，這時會將資料庫變成SUSPECT的狀態。所以我重新啟動 SQL Server，並嘗試去取得DemoSuspect資料庫。


USE DemoSuspect;GO
Msg 945, Level 14, State 2, Line 1
Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


現在讓我們再檢查資料庫的狀態：


SELECT DATABASEPROPERTYEX('DemoSuspect', 'STATUS') AS 'Status';GO
Status
-------
SUSPECT

在此讓我們來看如何正確的從備份檔來還原資料庫。如果沒有可用的備份，下一個步驟是從Emergency Mode的狀態中從資料庫取得資料，並嘗試進行修復。無論如何，我將會去嘗試 卸除/附加(attach/detach) 資料庫的動作。


2、卸除資料庫
我將嘗試卸除資料庫透過 sp_detach_db


EXEC sp_detach_db 'DemoSuspect';GO

Msg 947, Level 16, State 1, Line 1
Error while closing database 'DemoSuspect'. Check for previous additional errors and retry the operation.

現在我們來看看是否可以執行？


SELECT * FROM sys.databases WHERE NAME = 'DemoSuspect';GO

執行後是沒有資料回傳，所以卸除資料庫是有成功的。
備註：從 SQL SERVER 2008 你不能夠卸除一個有問題的資料庫，除非你有足夠的時間處理其他的事。(如設定資料庫為離線，然後複製檔案並 Drop 資料庫)‧


3、重新附加資料庫
現在嘗試去觀察 sp_attach_db：

EXEC sp_attach_db @dbname = N'DemoSuspect', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf';GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf' is not a valid database file header. The PageAudit property is incorrect.

如何使用 attach_rebuild_log 的功能重新建立資料庫呢？這將會建立一個新的 紀錄檔：


CREATE DATABASE DemoSuspect ON (NAME = DemoSuspect, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf')FOR ATTACH_REBUILD_LOG;GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf' is not a valid database file header. The PageAudit property is incorrect.File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf" may be incorrect.The log cannot be rebuilt because the database was not cleanly shut down.Msg 1813, Level 16, State 2, Line 1Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted.

資料庫都會附加一個主動的交易檔。使用 attach_rebuild_log 的命令只可以用在記錄檔是清空和有問題的情況下。什麼樣的情況下我可以移除紀錄檔和嘗試這個指令呢？可以可以欺騙他？我取得一個複製的資料檔和記錄檔，並且刪除原本的記錄檔(這真的是越來越狡滑了)。讓我們再試一次：


CREATE DATABASE DemoSuspect ON(NAME = DemoSuspect, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf')FOR ATTACH_REBUILD_LOG;GO

File activation failure.
The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf" may be incorrect.The log cannot be rebuilt because the database was not cleanly shut down.Msg 1813, Level 16, State 2, Line 1 Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted.


嗯，這一槍沒打中但是SQL Server真的變聰明了。
基本上這個問題資料庫沒有完整的關閉，意義上代表了復原是可以執行和完成的在資料庫再次附加之前。但是取得的記錄檔是壞掉的，那是不可能的。
所以，絕對不要卸除一個有問題的資料庫。



只有一個方法讓資料庫回復到SQL Server之中，如同高手(Hack)般的技巧。我將去建立一個新的虛擬資料庫與相同大小檔案配置的卸除資料庫。然後再將SQL Server的關閉，置換掉有問題的資料庫檔案，並且重新啟動SQL Server。如果一切順利的話，這個有問題的資料庫將可以再次附加上。

在這有一個主要的缺點，如果SQL Server instance沒有即時的進行初使化，建立虛擬資料庫時如果資料檔很大時就會進行的非常的久。這個意思上代表你的應用程式可能會造成很久的離線時間。所以我已經取得一個有問題的資料庫複本，所以我現在需要去刪除資料檔案。但在刪除之前請確認你已有拿到正確的複本檔案。在這個案例上，刪除資料檔案之後，我就能夠建立我的虛擬資料庫。


CREATE DATABASE DemoSuspectGO;

如果你忘記去刪除存在的問題檔案時，你將會取得下列的錯誤訊息：

Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.Msg 5170, Level 16, State 1, Line 1Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf' because it already exists. Change the file path or the file name, and retry the operation.


刪除檔案再嘗試一次，現在需要去檢查虛擬資料庫是有建立成功(在這一點上非常重要)，關閉SQL Server，刪除虛擬資料庫檔案(這個會看到警告訊息)，然後交換問題的檔案。我複制了另外一個損壞的檔案在他們交換之前，藉以防止萬一發生錯誤。
在SQL Server重新啟動之後，我可以檢查資料庫的狀態：


SELECT DATABASEPROPERTYEX ('DemoSuspect', 'STATUS') AS 'Status';GO

Status
--------
SUSPECT


我再次將有問題的資料庫再次附加上，之後進行關機，但是發生刪除和複制檔案混亂的情況，真的是不太好。現在我開始進行修正。

4、修復資料庫

如果你沒有任何的備份檔，只好將資料庫切換至緊急模式。這樣就可以進入到資料庫中，但是你需要了解到這樣的復原方式可能會造成內容結構上的不完整。


ALTERDATABASE DemoSuspect SET EMERGENCY; GO
ALTER DATABASE DemoSuspect SET SINGLE_USER;GO
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoSuspect_log.LDF' is not a valid database file header. The PageAudit property is incorrect.File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoSuspect_log.LDF" may be incorrect.The log cannot be rebuilt because the database was not cleanly shut down.The Service Broker in database "DemoSuspect" will be disabled because the Service Broker GUID in the database (9E879BFC-B742-4A69-AB14-4D6BD6F99E02) does not match the one in sys.databases (B4568D23-7018-40CF-B189-9C29DE697C09).Warning: The log for database 'DemoSuspect' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

首先試圖去進行一般的 attach_rebuild_log。當失敗發生時，DBCC CHECKDB 接管和負責重新建立紀錄檔，盡可能的試圖去修復損壞的記錄檔，因為他是可能可以修復成功的(完整)。他執行一個完全修復，在案例上任何的損壞在資料庫上，在這種情況下也不會有任何關於損壞的訊息輸出。
注意到Service Broker GUID是有錯誤的。我透過一些較特別的方式(Hack)去進行資料庫的附加，但是當我建立虛擬資料庫時，他建立一個DemoSuspect資料庫的Service Broker GUID 在 master.sys.databases。當我交換有問題的資料庫時，會造成GUID的不同，所以現在我不能夠使用Service Broker!!這一切都是因為我是卸除有問題的資料庫而不是修復他。
所以資料的狀態為何呢？


USE DemoSuspect;GO
SELECT * FROM Employees;GO

FirstName   LastName   YearlyBonus
-----------------------------------------------------
Paul            Randal              10000
Kimberly      Tripp                        0


上述的資料中 [Kimberly] 今年沒有拿到任何的獎金 - 他應該會很不高興吧!!這是因為人為與輕率所造成，當然，他描繪出重點在緊急模式進行修復，交易在執行時候可能沒有機會進行回復(Roll-Back)，最有可能。在這個案例中，我明白當問題產生時發生了那些事，但一個忙碌的OLTP系統有幾百或幾千個執行中的交易？我該如何知道資料的狀態呢？

5、總結
是的，你可以回復從一個已卸除有問題的資料庫中，但是他並非完美必須非常注意他。最好的行動方針還是你必須有一個好的完整備份策略，將允許你快速的還原資料庫。如果你有一個問題的資料庫而且沒有備份，透過緊急模式去存取或修復資料庫。希望這個文章將幫助大家找到一個好的解決方法。

原始網址：http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

關鍵字：SQL Server、Suspect Database、Repair Database、Detach / Attach Database