SQLServer 2008以上誤操作資料庫恢複方法——日誌尾部備份

標籤：

原文: SQLServer 2008以上誤操作資料庫恢複方法——日誌尾部備份

原文出處：http://blog.csdn.net/dba_huangzj/article/details/8491327問題：

         經常看到有人誤刪資料，或者誤操作，特別是update和delete的時候沒有加where，然後就喊爹喊娘了。人非聖賢孰能無過，做錯可以理解，但不能縱容，這個以後再說，現在先來解決問題。

        遇到這種情況，一般都是沒有做備份，不然也不會來發問了。首先要冷靜，否則會有更大的災難。直到你放棄。

解決方案：

       對於這類問題，主要是找回誤操作之前的資料，在2008之前，有個很出名的工具Log Exploer，聽說還挺好用的，這個網上大把教程，這裡就不多說了。但是唯一遺憾的是，不支援2008及更高版本，這時除了其他第三方工具，那麼最常用的就是本文提到的方法——日誌尾部備份。本文實驗環境2008R2，對於2008及其以上版本可以使用這個方法，其實2005也可以，2000很少用，沒試過，只是2008之前可以使用Log Exploer，所以就沒必要用這種方法。

      下面圖文並茂講解操作方法，至於原理，不屬於本文範圍，而且我相信真遇到誤操作的時候，估計沒人會看原理了。

步驟：（1）、檢查資料庫的復原模式，

或者使用指令碼檢查：

SELECT recovery_model,recovery_model_descFROM sys.databasesWHERE name =‘AdventureWorks‘

結果如下：

        確保資料庫的復原模式最起碼不能為【簡單】。至於如何修改成完整模式，我覺得這些應該沒必要多說了。

 

       切記，對於任何重要環境，不僅僅是客戶正式環境（俗稱生產環境），都強烈建議使用【完整復原模式】，雖然對於另外兩種（大容量日誌（BULK_LOGGED）、簡單（SIMPLE））來說，完整復原模式產生的日誌會大，但是在出現問題的時候，就會覺得這些都不算什麼了。並且我也想不到任何理由對於正式環境不使用完整復原模式。只要管理得當，完整復原模式的日誌也不會太變態。

 

（2）、這裡其實隱含另外一步，曾經做過最少一次的完整備份。因為所有類型的備份都基於完整備份，如果沒有最少一次完整備份，其他類型的備份都是多餘的，所以在這裡強調一下，在建立完一個新資料庫之後，強烈建議甚至強製做一次完整備份。

SELECT  database_name,recovery_model,name FROM msdb.dbo.backupset

使用上面的語句粗略可以看到有那些資料庫做過備份，由於測試，所以做了幾次備份，可以看到我這個時間點已經做了備份了。

（3）、確保別人不再串連資料庫，然後做一次日誌尾部備份：

首先先建立一點資料：

/*由於tempdb永遠為簡單復原模式，所以不適合做案例。這裡使用微軟的樣本資料庫AdventureWorks*/USE AdventureWorksGOIF OBJECT_ID(‘testRestore‘) IS NOT NULL     DROP TABLE testRestoreGOCREATE TABLE testRestore    (      id INT IDENTITY(1, 1) ,      NAME VARCHAR(50)    );--插入測試資料：   INSERT INTO testRestore(Name)SELECT ‘test1‘UNION ALL SELECT ‘test2‘UNION ALL SELECT ‘test3‘UNION ALL SELECT ‘test4‘UNION ALL SELECT ‘test5‘UNION ALL SELECT ‘test6‘UNION ALL SELECT ‘test7‘UNION ALL SELECT ‘test8‘SELECT * FROM testRestore

檢查一下結果：

然後來做個刪除操作，為了定位是啥時候發生的，我加了一個waitfor命令，讓它在某個時間發生，這樣恢複的時候就有準確性：

USE AdventureWorksGOWAITFOR TIME ‘21:45‘DELETE FROM dbo.testRestore

現在來看看資料：

USE AdventureWorksGOSELECT * FROM dbo.testRestore

到這一步，災難出現了。但是切記要冷靜。

下面就是本文的重點開始，做一次記錄備份，最重要是選擇【備份日誌尾部】

然後在【選項】頁選擇：除【交易記錄】除，其他紅框包裹的地方為強烈建議勾選的地方。並且保證資料庫不要有別人在串連，因為備份日誌尾部會使資料庫處於還原狀態，拒絕其他會話的串連，如果不斷開其他串連，是備份不了的。

然後按確定，當然，可以使用上方的【指令碼】來產生語句：

USE MasterGOBACKUP LOG [AdventureWorks] TO  DISK = N‘E:\AdventureWorks.bak‘ WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N‘AdventureWorks-交易記錄 備份‘, SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10, CHECKSUMGOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=N‘AdventureWorks‘ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N‘AdventureWorks‘ )if @backupSetId is null begin raiserror(N‘驗證失敗。找不到資料庫“AdventureWorks”的備份資訊。‘, 16, 1) endRESTORE VERIFYONLY FROM  DISK = N‘E:\AdventureWorks.bak‘ WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDGO

此時，資料庫會處於【正在還原】的狀態

如果發現備份不了可以用下面語句查看，並把spid殺掉：

SELECT  * FROM sys.sysprocesses WHERE dbid=DB_ID(‘AdventureWorks‘)

執行結果：

然後kill掉。

接著繼續備份。

 

然後進行還原，

先要還原完整備份，選擇最近的那次，由於記錄備份的特性（以後其他文章再說），只認最後一次備份，所以要選擇最新的那次，否則還原不了。

這裡又有一個注意事項，記得選擇：

接著還原記錄檔，這是最最重要的一步：

然後：

由於實驗的時候出了點問題，後面重做了，所以時間選擇到22：19分，我是在22：20分刪除資料的。這裡不用太在意，只要把時間點指定到你誤刪除的時間之前即可。而由於日誌尾部備份都是最後一個備份檔案，所以這裡選則紅框部分即可：

現在再檢查一下：

可以看到，資料已經還原成功。

 

總結：

平時不做備份，出問題來喊急，這是苟有自取，還有一些腦袋發熱的人喜歡看到ldf很大就直接刪除，那以後出問題就別怪微軟了。

本文中的方法看上去有點繁瑣，但是實操幾次就覺得好了，但是步驟建議嚴格按照上面說的，因為一旦操作錯誤，就很麻煩，此時再次強調——冷靜冷靜再冷靜！！！！！！

這種方法有幾個缺點：

1、             如果你發現誤操作以後還有很多人做了操作，那麼你還原成功後，別人的操作就會衝掉，所以發生誤操作後，要馬上停止別人對資料庫的操作。

2、             這個方法要對資料庫獨佔，所以你想偷偷恢複是不行的了。勇敢承認錯誤吧。

對於核心資料表，還是要先做好預防操作，可以看：SQLServer恢複表級資料。

關於備份，可以看我的另外一篇文章：第一篇——第一文 SQL Server 備份基礎

SQLServer 2008以上誤操作資料庫恢複方法——日誌尾部備份