淺析SQL數(shù)據(jù)庫修復(fù)技術(shù)

引言：SQL Server數(shù)據(jù)庫在實際工作中應(yīng)用的很廣泛，在管理SQL Server數(shù)據(jù)庫時，有時可能因為各種原因，會導(dǎo)致數(shù)據(jù)庫出現(xiàn)無法訪問等損壞故障。如果之前有數(shù)據(jù)庫備份的話，就很容易恢復(fù)數(shù)據(jù)庫的正常運行。但是，如果沒有對其進行及時有效的備份，那就只能對其進行修復(fù)操作。

數(shù)據(jù)庫可謂是存儲數(shù)據(jù)的大倉庫，這對于保證網(wǎng)站和企業(yè)內(nèi)網(wǎng)的安全運行至關(guān)重要。數(shù)據(jù)庫損壞是一種物理層面的損壞，即使進行修復(fù)，出現(xiàn)數(shù)據(jù)丟失的情況在所難免。單純使用修復(fù)手段，是無法完美的將數(shù)據(jù)庫恢復(fù)如初的。因此，使用正確的數(shù)據(jù)庫備份恢復(fù)策略，才可以從根本上數(shù)據(jù)庫的安全。

數(shù)據(jù)庫損壞的原因

我們知道，SQL Server是按照一定的格式將數(shù)據(jù)存儲在文件中的，當SQL Server訪問這些數(shù)據(jù)時，就會將其從物理磁盤中的存儲單元讀出，并按照固定的格式將其中的數(shù)據(jù)解析出來，如果在讀取磁盤或者解析數(shù)據(jù)時出現(xiàn)了問題，就會造成數(shù)據(jù)無法正常讀出的情況，這就說明數(shù)據(jù)庫發(fā)生了損壞。如果問題出現(xiàn)在日志文件中，就會導(dǎo)致數(shù)據(jù)庫恢復(fù)失敗。如果問題出現(xiàn)在數(shù)據(jù)庫文件中，雖然可以正常恢復(fù)數(shù)據(jù)，但是在訪問其中的數(shù)據(jù)時，就會出現(xiàn)各種問題。這就涉及到數(shù)據(jù)庫的修復(fù)操作，在修復(fù)數(shù)據(jù)庫時，有可能造成數(shù)據(jù)庫數(shù)據(jù)的丟失的問題。使用“DBCC CHECKDB”指令，可以檢查數(shù)據(jù)庫中是否存在損壞的情況，對于出現(xiàn)問題的數(shù)據(jù)庫，可以盡力對其進行修復(fù)，使其能夠重新進行訪問。

數(shù)據(jù)庫受損分為各種情況，例如是日志文件損壞的話，需要重建數(shù)據(jù)庫日志文件，這是不得已的辦法，可能破壞數(shù)據(jù)庫應(yīng)用的一致性。如果是數(shù)據(jù)庫文件損壞，又分為多種情形，例如對于非聚集索引頁面受損的情況，可以重建非聚集索引，嘗試對數(shù)據(jù)庫進行丟失數(shù)據(jù)的修復(fù)。對于聚集索引頁面受損的情況，可以采用帶有“REPAIR_ALLOW_DATA_LOSS”參數(shù)的DBCCCHECKDB命令，對數(shù)據(jù)庫進行修復(fù)，但可能出現(xiàn)數(shù)據(jù)丟失的情況。如果是系統(tǒng)頁面或者系統(tǒng)表受損，則只能從備份文件中恢復(fù)的方法，對數(shù)據(jù)庫進行修復(fù)。實際上，因為SQL Server自身問題導(dǎo)致數(shù)據(jù)庫受損的可能是比較低的，如果一臺服務(wù)器上的數(shù)據(jù)庫經(jīng)常發(fā)生損壞，就需要對Windows系統(tǒng)自身的運行情況進行檢測，特別是對I/O子系統(tǒng)進行檢測，來發(fā)現(xiàn)磁盤讀寫緩沖區(qū)，數(shù)據(jù)通道，驅(qū)動程序等環(huán)節(jié)是否存在問題，因為很大比例上的數(shù)據(jù)庫損壞問題都是由于I/O子系統(tǒng)故障所導(dǎo)致。

因此，對I/O子系統(tǒng)進行檢測是很有必要的，可以使用SQLIOSim這款小工具，通過模仿SQL Server的行為，以一定的并發(fā)負載觸發(fā)各種SQL Server相關(guān)的I/O動作，來驗證這些I/O是否被正確執(zhí)行。如果系統(tǒng)無法通過該工具的測試，就說明存在I/O子系統(tǒng)的相關(guān)問題?？梢栽诓渴餝QL Server服務(wù)器之前，利用該工具對系統(tǒng)進行檢測，確保I/O子系統(tǒng)不存在問題。隨著硬件技術(shù)的飛速發(fā)展，SQL Server的運行效能不斷提高，對I/O層面的壓力隨之增大，因此，對相關(guān)硬件（例如硬盤等）設(shè)備進行固件的升級，安裝更新的驅(qū)動程序等操作，可以提高I/O設(shè)備的性能，來更好的應(yīng)對上述問題。

在SQL Server層面上其實已經(jīng)添加了殘缺頁保護，校驗和保護等I/O驗證功能，可以幫助用戶更好更快的捕捉到數(shù)據(jù)庫錯誤的發(fā)生，讓用戶及時采取修復(fù)操作，保證數(shù)據(jù)庫的安全。對應(yīng)的命令格式為“Alter database 數(shù)據(jù)庫名 page_veryfy{checksum| torn_page_detection |None}”,例 如 執(zhí) 行“Alter database 數(shù)據(jù)庫名page_veryfy checksum”語句，可以激活校驗和保護功能。之后SQL Server根據(jù)每個頁面的內(nèi)容計算出一個校驗和，并在頁面被寫入磁盤時將該值保存在頁面頭部。當從磁盤讀出該頁面時，會重新計算出一個校驗和并與保存在頁面頭部的值相比較。如果這兩個值并不匹配，錯誤信息824(表示校驗和錯誤)會被報告出來。

解析DBCC CHECKDB命令的功能

使用DBCC CHECKDB指令，可以執(zhí)行一系列的操作，來檢查數(shù)據(jù)庫中所有對象的邏輯和物理的完整性。例如，可以對一些關(guān)鍵的系統(tǒng)表進行檢測，這些數(shù)據(jù)表（即元數(shù)據(jù)）對普通用戶來說是不可見的，其不會占用太多的頁面，發(fā)生問題的可能性不大。系統(tǒng)表一旦損壞，DBCC CHECKDB恢復(fù)操作就會失敗，只能使用備份文件進行恢復(fù)。之后該指令會檢查數(shù)據(jù)庫中所有的頁面分配，驗證各種內(nèi)部結(jié)構(gòu)，對數(shù)據(jù)庫中所有表和視圖進行檢查，檢查其是否正確連接索引，索引是否按照正常的方式排列，指針是否一致，頁面偏移量是否合理等。

接下來對數(shù)據(jù)庫執(zhí)行DBCC CHECKALLOC檢查，檢測數(shù)據(jù)庫中數(shù)據(jù)表記錄元數(shù)據(jù)的邏輯一致性，檢測每個索引視圖的內(nèi)容，檢測Service Broker相關(guān)對象是否正常等。如果發(fā)現(xiàn)數(shù)據(jù)庫存在問題，該指令可以對其進行修復(fù)操作。默認情況 下，DBCC CHECKDB指揮驗證數(shù)據(jù)庫是否完好，不會主動執(zhí)行修復(fù)數(shù)據(jù)庫操作，當嘗試修復(fù)數(shù)據(jù)庫時，需要使用相關(guān)的修復(fù)選項，例如對于“REPAIR_ALLOW_DATA_LOSS”選項來說，會嘗試修復(fù)報告的所有錯誤，這樣的話，可能會導(dǎo)致數(shù)據(jù)庫丟失一些數(shù)據(jù)?！癛EPAIR_FAST”選項主要為了向后兼容，不會執(zhí)行任何修復(fù)操作，無需加以使用，“REPAIR_REBUILD”選項的作用是執(zhí)行次要的，快速的修復(fù)行為，例如修復(fù)非聚集索引中的額外鍵等，以及執(zhí)行耗時修復(fù)來重新生成索引等。這行這樣的修復(fù)不存在丟失數(shù)據(jù)的風(fēng)險。

在執(zhí)行實際的修復(fù)時，必須將目標數(shù)據(jù)庫的用戶連接斷開，將目標數(shù)據(jù)庫設(shè)置為單用戶模式，將數(shù)據(jù)庫設(shè)置為緊急修復(fù)狀態(tài)。之后再進行具體的修復(fù)操作。注意，對數(shù)據(jù)庫執(zhí)行DBCC CHECKDB檢測時，可能花費的時間較長。例如對于沒有問題的大小為1TB的數(shù)據(jù)庫文件來說，檢測可能需要花費幾小時的時間，對于只有幾百兆，但是存在一些錯誤的數(shù)據(jù)庫來說，檢測可能會花費十幾個小時甚至更長的時間。如果在數(shù)據(jù)庫中設(shè)計了分區(qū)表機制，實現(xiàn)檢測操作就比較簡單。對于存儲歷史數(shù)據(jù)的分區(qū)文件組，可以將文件組類型設(shè)置為只讀模式，每月定期對其進行DBCCCHECKFILEGROUP操作即可。對于當前的數(shù)據(jù)，可以在每星期定期單獨進行DBCC CHECKFILEGROUP操作。對于沒有分區(qū)機制的大型數(shù)據(jù)庫來說，運行DBCC CHECKDB操作就比較耗時了。解決的方法是使用另外一臺服務(wù)器，將正常使用的數(shù)據(jù)庫備份恢復(fù)到該機上，在該機上執(zhí)行DBCC CHECKDB檢測即可。之后在備份服務(wù)器上使用DBCC CHECKDB指令，對數(shù)據(jù)庫進行檢測，如果發(fā)現(xiàn)問題，就說明至少在數(shù)據(jù)庫的備份時間點，數(shù)據(jù)庫就已經(jīng)存在故障了。

數(shù)據(jù)庫修復(fù)實例分析

這里就以SQL Server 2014為例，來說明如何修復(fù)數(shù)據(jù)庫。例如，當打開目標數(shù)據(jù)庫時，SQL Server彈出“SQL Server檢測到基于一致性的邏輯I/O錯誤不正確”的警告信息，提示在數(shù)據(jù)庫文件特定偏移量位置，對數(shù)據(jù)庫某ID頁的執(zhí)行讀取期間有錯誤發(fā)生，并提示這是一個威脅數(shù)據(jù)庫完整性的嚴重錯誤，必須立即糾正。在SQL Server Mangment Studio窗口左側(cè)選擇目標數(shù)據(jù)庫，在工具欄上點擊“新建查詢”按鈕，輸入并執(zhí)行“dbcc checkdb”命令，在窗口底部的“消息”面板中出現(xiàn)大量的紅色的錯誤信息。因為沒有及時創(chuàng)建數(shù)據(jù)庫備份，所以無法完美的恢復(fù)數(shù)據(jù)庫。這就要求在允許損失一些數(shù)據(jù)的情況下，盡可能的修復(fù)數(shù)據(jù)庫。在執(zhí)行修復(fù)之前，需要斷開用戶訪問。

輸入“DECLARE @temp NVARCHAR(20)”，“DECLARE myCurse CURSOR”，“FOR”，“SELECT spid”，“FROMsys.sysprocesses”，“WHERE dbid=DB_ID('xxxxxx')”，“OPEN myCurse”，“FETCH NEXT FROM myCurse INTO @temp”，“WHERE @@FETCH_STATUS = 0”，“BEGIN”，“EXEC('kill'+@temp)”，“FETCH NEXT FROM myCurse INTO @temp”，“ END”，“CLOSE myCurse”，“DEALLOCATE myCurse”等語句， 其中的“xxxxxx”表示具體的數(shù)據(jù)庫名稱。點擊工具欄上的“執(zhí)行”按鈕，執(zhí)行上述語句，其作用是關(guān)閉其他用戶進程。之后就可以執(zhí)行數(shù)據(jù)庫的修復(fù)操作了，例如輸入“USE Master”，“Go”，“DECLARE@Data basename varchar(255)”，“SET @Databasename='xxxxxx'”，“ALTER Database xxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE”，“DBCC Checkd b(@Databasename,REPAIR_ALLOW_DATA_LOSS)”，“DBCC Check(@Databasename,REPAIR_REBUILD)”，“ALTER Database xxxxxx SET MULTI_USER WITH ROLLBACK IMMEDIATE”等語句，其中的“ALTER Database xxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE”語句的作用是將目標數(shù)據(jù)庫設(shè)置為單用戶狀態(tài)。

對于SQL Server 2005版本的數(shù)據(jù)庫來說，可以將其替換為“EXEC SP_dboption @ databasename,N’single’,N’true’”語句。最后的“ALTER Database xxxxxx SET MULTI_USER WITH ROLLBACK IMMEDIATE”語句的作用是將目標數(shù)據(jù)庫設(shè)置為多用戶狀態(tài)。如果是SQL Server 2005版本，可以替換為“EXEC SP_dboption @databasename,N’single’,N’false’”實 現(xiàn)同樣的功能。執(zhí)行上述語句，執(zhí)行針對目標數(shù)據(jù)庫的修復(fù)操作。當然，在實際操作數(shù)據(jù)庫時，可能會遇到各種錯誤，例如當對目標數(shù)據(jù)庫執(zhí)行事務(wù)操作時，對數(shù)據(jù)庫某個表的內(nèi)容進行Update操作，之后執(zhí)行“CheckPoint”項，立即將修改的內(nèi)容寫入到數(shù)據(jù)文件中。之后執(zhí)行了一個延遲操作，然后在同一事務(wù)中又執(zhí)行了一個Update操作，對相同的數(shù)據(jù)表的內(nèi)容進行了更改。

當?shù)谝粋€修改完成后，第二個修改操作還沒有進行時，SQL Server主進程“sqlservr.exe”突然異常退出，并且因為某些原因，數(shù)據(jù)庫日志文件出現(xiàn)受損的情況，造成重啟SQL Server后，無法將前一個修改操作進行回滾，無法保持數(shù)據(jù)的一致性。這樣，當重新連接SQL Server后，在SQL Server Mangment Studio窗口中就會發(fā)現(xiàn)目標數(shù)據(jù)庫處于恢復(fù)掛起狀態(tài)。根據(jù)系統(tǒng)顯示的錯誤編號為“5105”，嚴重性為16，狀態(tài)為1等信息，說明日志文件出現(xiàn)了問題。點擊“管理”→“SQL Server日志”項，在其下選擇當前日志項目，在日志查看器窗口左側(cè)選擇合適的存檔編號，在日志中可以發(fā)現(xiàn)日志損壞的相關(guān)信息。例如無法重新生成日志等，其原因是數(shù)據(jù)庫異常關(guān)閉時存在打開的事務(wù)/用戶，該數(shù)據(jù)庫沒有檢查點或者該數(shù)據(jù)庫是只讀的等。此刻，如果沒有數(shù)據(jù)庫備份的話，只能對數(shù)據(jù)庫進行修復(fù)操作。

輸入并執(zhí)行“ALTER DATABASE TESTDB10 SET EMERGENCY”語句，將數(shù)據(jù)庫設(shè)置為緊急狀態(tài)，之后執(zhí)行上述語句，將數(shù)據(jù)庫設(shè)置設(shè)置為單用戶狀態(tài)，注意將其中的“WHERE dbid= DB_ID('xxxxxx')”修改為合適的數(shù)據(jù)庫名稱。之后執(zhí)行“ALTER DATABASE xxxxxx Rebuild LOG on (name=xxxxxx_log,filename='D:shujukuxxxxxx.LDF')”，“ALTER DATABASE xxxxxx SET MULTI_USER”等語句，對日志文件進行重建處理，并將數(shù)據(jù)庫恢復(fù)為多用戶狀態(tài)。對于受損的數(shù)據(jù)庫，系統(tǒng)會顯示“SQL Server檢測到基于一致性的邏輯I/O錯誤不正確”的警告信息，根據(jù)系統(tǒng)顯示的錯誤編號為824，嚴重性為24，狀態(tài)為2的信息。

可以執(zhí)行“ALTER DATABASE xxxxxx set EMGERGENCY”，“ALTER DATABASE xxxxx set single_user with rollback immediate”，“GO”，“DBCC CHECKD B(xxxxxx,'REPAIR_ALLOW_DATA_LOSS')”，“GO”，“ALTER DATABASE xxxxx set multi_user”等語句，可以對目標數(shù)據(jù)庫文件進行修復(fù)。當對目標數(shù)據(jù)庫進行刷新后，可以正常使用該數(shù)據(jù)庫。但是，對同一事務(wù)中數(shù)據(jù)操作會出現(xiàn)不一致的情況，雖然對數(shù)據(jù)庫邏輯上分析不存在問題，但是對數(shù)據(jù)庫的應(yīng)用存在明顯的問題，對于日志的修復(fù)可能破壞數(shù)據(jù)庫應(yīng)用的一致性，是迫不得已的方法。所以更根本上說，建議及時對數(shù)據(jù)庫進行備份并根據(jù)需要安全的恢復(fù)數(shù)據(jù)，這樣才能保證數(shù)據(jù)在業(yè)務(wù)邏輯上的一致性。如果數(shù)據(jù)庫出現(xiàn)損壞的情況，系統(tǒng)會顯示“SQL Server檢測到基于一致性的邏輯I/O錯誤不正確”的警告信息，根據(jù)系統(tǒng)顯示的錯誤編號為 824，928 嚴重性為 20，24，狀態(tài),1，2的信息。可以執(zhí)行“use master”，“go”，“SP_CONFIGURE 'ALLOW UPDATES',1”，“go”，“RECONFIGURE WITH OVERRIDE”，“go”，“ALTER DATABASE xxxxxx SET EMERGENCY”，“go”，“ALTER DATABASE xxxxxx set single_user with rollback immediate”，“GO”，“DBCC CHECKD B(xxxxxx,'REPAIR_ALLOW_DATA_LOSS')”，“GO”，“ALTER DATABASE xxxxxx set multi_user”，“go”等語句，對數(shù)據(jù)庫進行修復(fù)操作。