講解SQL Server 2005災難恢復步驟
作者: bianceng, 出處:IT專家網論壇, 責任編輯: 陳子琪,
2010-04-27 07:00
SQL Server 2005在實現(xiàn)高可用性和災難恢復方面給我們提供了很多種選擇。比技術本身更重要的是拿出適當?shù)某绦?，這是因為我們要管理不同的災難恢復方案。
問題
SQL Server 2005在實現(xiàn)高可用性和災難恢復方面給我們提供了很多種選擇。比技術本身更重要的是拿出適當?shù)某绦颍@是因為我們要管理不同的災難恢復方案。我們應該如何拿出程序來管理多種多樣的災難恢復場景呢?
專家解答
這一系列文章將探討不同的災難恢復場景和涉及你的恢復計劃的程序?？紤]到你的SQL Server 2005數(shù)據庫中的災難恢復選項，你應該包含盡可能多的技術，因為如果災難發(fā)生時你將會有很多種選擇來解決這個問題。盡管擁有這些技術被證實是很重要的，但是它也是一個伴隨著使整個程序更加有效的過程。在這篇文章里，讓我們來看看一個簡單的場景，那就是在發(fā)生數(shù)據庫備份的5個小時后，一個用戶不小心刪除了一張表。從一個數(shù)據庫備份中恢復意味著失去5小時的數(shù)據。而對于大多數(shù)的公司來說，比起失去數(shù)據，他們更愿意選擇失去時間。并且，如果這是一個非常大的數(shù)據庫，那么將花很長一段時間來恢復和使它聯(lián)機。在將數(shù)據損失控制到最小的前提下，我們將考慮這種情況來建立一個程序方法，以此來盡快地恢復數(shù)據庫。我們將使用Northwind數(shù)據庫來顯示這個過程。記住在進行以下步驟之前先把Northwind中的數(shù)據庫恢復模式改成FULL。
1) 確保你有一個好的備份
針對上面的場景，我們假設你有一個每天早上6:00運行的備份，并且沒有一個定期創(chuàng)建的數(shù)據庫快照。你的數(shù)據庫被配置成使用一個單一的MDF和LDF文件，這個文件對災難恢復不太適用。讓我們在Northwind數(shù)據庫中創(chuàng)建一個全數(shù)據庫備份，它將是數(shù)據庫恢復的起始點。
以下是代碼：
USEmaster
GO
BACKUPDATABASENorthwind
TODISK=N'D:DBBackupNorthwindBackup.bak'
WITHNAME=N'FullDatabaseBackup',DESCRIPTION='Startingpointforrecovery',
INIT,STATS=10
GO |
從Northwind數(shù)據庫模式中，你很難刪除Products, Orders和Customers表，這是因為有其他表比如Order Details表的外碼約束限制。但我敢打賭你可以輕易地刪除Order Details表。接下來，我們來模擬在大約上午11:00時刪除這張表的災難情景。
2) 包含發(fā)生的問題
由于這個數(shù)據庫只有一個單一的MDF和LDF文件，我們不可能做很多操作。我們所能做的就是通過把它設置成有限制的用戶模式來使數(shù)據庫脫機。
USEmaster
GO
ALTERDATABASENorthwind
SETRESTRICTED_USER
WITHROLLBACKIMMEDIATE
GO |
這將很有效地使數(shù)據庫脫機，停止所有活動的連接。這是分秒必爭的時刻，我們需要采取行動。當進行到下面的步驟時，請牢記你的RPO和RTO。
3) 備份事務日志
一個好的數(shù)據庫管理員應該知道當災難來臨時，首先要做的是備份事務日志 – 假設你的數(shù)據庫被設置成全數(shù)據庫備份。這是為了確保在上次備份之后你還保存所有活動的事務。在我們的場景中，由于上次的備份 – 全備份，在這個例子中 – 發(fā)生于早上6:00。
BACKUPLOGNorthwind
TODISK=N'D:DBBackupNorthwindBackupLog.trn'
WITHNAME=N'TransactionLogBackup'
,DESCRIPTION='Gettingeverythingtocurrentpointintime.',
STATS=10
GO |
4) 還原數(shù)據庫到一個已知的良好時間點
現(xiàn)在，任何用戶意外地刪除一張表或者在數(shù)據庫上造成一些破壞后都不會立即告訴你。有時候，你可能需要自己深入挖掘它，但是這需要花很多時間。由于我們想讓數(shù)據庫盡快地聯(lián)機，所以我們假設一個眾所周知的良好時間點，并且讓發(fā)掘在稍后的時間里進行。在下面的腳本中，我將選擇在我的STOPAT參數(shù)中將早上10:42設置成已知的良好時間點，一次達到展示的目的。
RESTOREDATABASENorthwind
FROMDISK=N'D:DBBackupNorthwindBackup.bak'
WITHNORECOVERY,RESTRICTED_USER
GO
RESTORELOGNorthwind
FROMDISK=N'D:DBBackupNorthwindBackupLog.trn'
WITHRESTRICTED_USER,
STOPAT='2008-09-2310:42:44.00',RECOVERY
--usea"knowngood"pointintime
GO |
雖然我們已經把數(shù)據庫恢復到一個已知的良好時間點，但是我們并沒有知道我們實際上到底失去了多少數(shù)據。我們需要找出在執(zhí)行DROP TABLE語句之前執(zhí)行最后的一個INSERT語句的準確時間，這樣我們才能恢復盡可能多的數(shù)據。但是由于我們需要盡可能快地使數(shù)據庫聯(lián)機，所以我們不想在數(shù)據庫上直接做這些。這正證明了接下來的步驟是有價值的。
你可以通過執(zhí)行一個針對它的查詢來驗證刪除的表是否已經恢復。
SELECT*
FROMNorthwind.dbo.[OrderDetails]
GO |
5) 創(chuàng)建一個恢復點的快照
我們將創(chuàng)建一個還原數(shù)據庫的數(shù)據庫快照，以此來做進一步的處理。這個數(shù)據庫快照將是把數(shù)據恢復到執(zhí)行DROP TABLE語句之前的準確時間的參考。
USEmaster
GO
CREATEDATABASENorthwind_RestorePointSnapshot
ON
(NAME=N'Northwind',
FILENAME=N'D:DBBackupNorthwindData_RestorePontSnapshot.snap')
ASSNAPSHOTOF[Northwind]
GO |
依靠這個表模式，我們可以選擇讓它保持原狀，或者像我們對Order Details 表所做的那樣，或者多做一些操作。如果這張表有一個現(xiàn)有IDENTITY欄，我們需要在IDENTITY欄的最大值和需要恢復的行的假設數(shù)量之間創(chuàng)建一個間隙。這當然取決于在服務器上發(fā)生的事務數(shù)目。要確定IDENTITY欄的最大值，你可以執(zhí)行如下顯示的DBCC CHECKIDENT命令：
DBCCCHECKIDENT('tableName')
--Displaysthenumberofrowsyouhavefortherestoredtable
GO |
這將返回IDENTITY列的最大值。讓我們假設這張表每天的事務數(shù)目大約是4000條記錄，我們可以在最大值和下一個值之間創(chuàng)建一個間隙。如果IDENTITY列的最大值是25000，我們需要將4000加到這個值中，并且再次執(zhí)行帶有RESEED參數(shù)的DBCC CHECKIDENT命令(我們簡單假設你在一天之內可以恢復失去的數(shù)據，那就是值為400)：
DBCCCHECKIDENT('tableName',RESEED,29000)
--CreatesagapoffortheIDENTITYcolumntostartthenextvalueat29000
GO |
6) 使數(shù)據庫聯(lián)機
一旦你已經成功做到上面的步驟，那么就更改數(shù)據庫選項使它聯(lián)機并且允許用戶連接到數(shù)據庫和運行他們的應用程序。
USEmaster
GO
ALTERDATABASENorthwind
SETMULTI_USER
GO |
現(xiàn)在數(shù)據庫已經重新聯(lián)機并且刪除的表已經恢復。雖然現(xiàn)在每個人都很開心，但是作為數(shù)據庫管理員，我們的工作不能就此止步。記住我們仍然需要恢復從已知良好時間點到執(zhí)行DROP TABLE命令之前的缺失數(shù)據。這是我們能夠恢復盡可能多的數(shù)據的唯一途徑。盡管我們可以用一些第三方工具來讀取事務日志和通過重放這些事務來恢復數(shù)據，但是絕大多數(shù)人沒有使用這些工具的奢侈權利。所以我們下一個最好的方法是使用帶有STOPAT選項的RESTORE從句。這可能有些繁瑣，有時也很讓人有壓力，因為一個錯誤會讓你陷入重復地執(zhí)行整個過程的狀態(tài)。我們所需要做的就是找出我們做備份的時間，直到事務日志備份結束。在我們的場景中，上次的備份在早上6:00，而你的已知良好時間點在早上10:42.44。因此，你可以從早上10:42.44開始執(zhí)行帶有STOPAT選項的RESTORE命令，并且把STOPAT時間值改成可能一秒。如果你不是很確定上次備份發(fā)生的時間，你可以一直查詢MSDB數(shù)據庫。
SELECT*
FROMmsdb.dbo.backupsetASa
INNERJOINmsdb.dbo.backupmediafamilyASb
ONa.media_set_id=b.media_set_id
WHEREdatabase_name='Northwind'
ORDERBYbackup_finish_date |
請注意backup_finish_date和type欄，因為這些欄將會讓你知道什么時候你需要考慮RESTORE命令中的STOPAT值。
7) 用不同的名稱恢復另一個損壞數(shù)據庫的副本以便于研究
用不同的名稱來還愿損壞數(shù)據庫的另一個副本可以讓我們致力于數(shù)據的還原，而不用擔心可用性，這是因為我們已經成功建立一個生產數(shù)據庫。你只要確定你為恢復數(shù)據庫選擇了不同的名稱和數(shù)據庫文件位置，或者你可以結束損壞已經建立的數(shù)據庫。我們將重復步驟四所做的，但是在這里使用不同的名稱和數(shù)據文件位置。
RESTOREDATABASENorthwind_recover
FROMDISK=N'D:DBBackupNorthwindBackup.bak'
WITHMOVEN'Northwind'TON'D:DBBackupNorthwindData_recover.mdf',
MOVEN'Northwind_Log'TON'D:DBBackupNorthwindLog_recover.ldf',
STANDBY=N'D:DBBackupNorthwind_UNDO.bak',
STATS=10
GO
RESTORELOGNorthwind_recover
FROMDISK=N'D:DBBackupNorthwindBackupLog.trn'
WITHSTANDBY=N'D:DBBackupNorthwind_UNDO.bak',
STATS=10,STOPAT='2008-09-2310:42:44.00'
GO |
記錄你的STOPAT參數(shù)值，因為這將是你在這個過程中使用到的最關鍵的參數(shù)。由于我們只是重復了步驟四的過程，我們肯定知道這個時候還沒有執(zhí)行DROP TABLE命令。
8) 通過提高STOPAT參數(shù)值來還原事務日志
我們執(zhí)行RESTORE LOG命令，通過一分鐘 – 從10:42:44.00到 10:43:44.00來增加STOPAT參數(shù)值。
RESTORELOGNorthwind_recover
FROMDISK=N'D:DBBackupNorthwindBackupLog.trn'
WITHSTANDBY=N'D:DBBackupNorthwind_UNDO.bak',
STATS=10,STOPAT='2008-09-2310:43:44.00'
GO |
這是它重復的部分。不要因為它確實繁瑣而感到煩惱。你可以通過1分鐘，5分鐘，10分鐘和記錄這些時間來增加這個值。記住在執(zhí)行RESTORE LOG命令之后在刪除的對象上運行一個測試查詢。我建議在這個活動上創(chuàng)建一張表，這張表看起來像這樣：
TIMEOBJECTEXISTED?
10:43:44.00YES
10:48:44.00YES
10:58:44.00YES
11:03:44.00NO |
有了這個信息，你就可以很肯定地知道這張表在10:58:44.00與 11:03:44.00之間被刪除。你可以重復步驟8，并且因為你已經在一個更小的時間間隔內操作，你可以在一分鐘甚至一秒內增加STOPAT參數(shù)值，如果你覺得自己過度關注STOPAT參數(shù)的時間值，那么請返回帶有你在步驟八中記錄的列表信息的步驟七，使恢復過程更快進行。你只需要記住在上次的RESTORE LOG語句中使用WITH RECOVERY選項，例如：
RESTORELOGNorthwind_recover
FROMDISK=N'D:DBBackupNorthwindBackupLog.trn'
WITHSTATS=10,STOPAT='2008-09-2311:01:44.00',RECOVERY
GO |
一旦你設法把數(shù)據庫恢復到執(zhí)行DROP TABLE命令之前，你就可以在生產數(shù)據庫上所做的還原和恢復所做的作一個比較。你可以用很多種方式實現(xiàn)這一點。由于我們已經有一個之前創(chuàng)建好的數(shù)據庫快照，所以我們也用它來做災難恢復。先前關于SQL Server 2005 tablediff命令行工具的方法可以讓你可以了解如何使用這個工具但是只強調你的源數(shù)據庫將是你恢復的那個數(shù)據庫，并且目的數(shù)據庫將是你的數(shù)據庫快照。這是為什么你的數(shù)據庫快照被證實是很重要的，尤其是如果你處理不只是一個平常的對象。如果你不習慣用命令行工具，那么可以參考一個由來自SQLTeam.com的成員創(chuàng)建的GUI版本。你可能也想確定這些并且把它放在你的DBA工具欄中。
你也可以執(zhí)行一個INSERT/SELECT操作，在基于恢復數(shù)據庫的一個查詢的生產數(shù)據庫上插入紀錄。因為我們的Order Details表沒有IDENTITY欄，所以可以通過在一張臨時表中插入紀錄和使用ROW_NUMBER()函數(shù)來自己創(chuàng)建這一欄。
--Thisinsertsrecordsinatemporarytableandassignsadummyidentityvalueforreference
SELECTROW_NUMBER()OVER(ORDERBYOrderID)ASROWID,*
INTONorthwind_recover.dbo.OrderDetailsRecover
FROM[OrderDetails]
--Thisinsertsrecoveredrecordsfromtherecovereddatabaseintotheproductiondatabasebasedon
--thedummyidentityvaluewehaveassignedforreference
INSERTINTONorthwind.dbo.[OrderDetails](OrderID,ProductId,UnitPrice,Quantity,Discount)
SELECTOrderID,ProductId,UnitPrice,Quantity,Discount
FROMNorthwind_recover.dbo.OrderDetailsRecover
WHEREROWID>
(
SELECTCOUNT(*)
FROMNorthwind_RestorePointSnapShot.dbo.[OrderDetails]
) |
請注意，我們使用數(shù)據庫快照來識別我們所設法還原的和我們所恢復的區(qū)別。
