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

標籤：des   blog   http   io   ar   os   使用   sp   for   

問題：

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

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

 

解決方案：

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

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

步驟：

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

 

 

或者使用指令碼檢查：

1. SELECT recovery_model,recovery_model_desc  
2. FROM sys.databases  
3. WHERE name =‘AdventureWorks‘  

 

結果如下：

 

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

 

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

 

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

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

 

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

 

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

首先先建立一點資料：

1. /*  
2. 由於tempdb永遠為簡單復原模式，所以不適合做案例。  
3. 這裡使用微軟的樣本資料庫AdventureWorks  
4. */  
5. USE AdventureWorks  
6. GO  
7. IF OBJECT_ID(‘testRestore‘) IS NOT NULL   
8.     DROP TABLE testRestore  
9. GO  
10. CREATE TABLE testRestore  
11.     (  
12.       id INT IDENTITY(1, 1) ,  
13.       NAME VARCHAR(50)  
14.     );  
15. --插入測試資料：     
16. INSERT INTO testRestore(Name)  
17. SELECT ‘test1‘  
18. UNION ALL   
19. SELECT ‘test2‘  
20. UNION ALL   
21. SELECT ‘test3‘  
22. UNION ALL   
23. SELECT ‘test4‘  
24. UNION ALL   
25. SELECT ‘test5‘  
26. UNION ALL   
27. SELECT ‘test6‘  
28. UNION ALL   
29. SELECT ‘test7‘  
30. UNION ALL   
31. SELECT ‘test8‘  
32. SELECT * FROM testRestore  

檢查一下結果：

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

1. USE AdventureWorks  
2. GO  
3. WAITFOR TIME ‘21:45‘  
4. DELETE FROM dbo.testRestore  

 

現在來看看資料：

1. USE AdventureWorks  
2. GO  
3. SELECT * FROM dbo.testRestore  

 

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

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

 

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

 

 

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

 

1. USE Master  
2. GO  
3. BACKUP LOG [AdventureWorks] TO  DISK = N‘E:\AdventureWorks.bak‘ WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N‘AdventureWorks-交易記錄 備份‘, SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10, CHECKSUM  
4. GO  
5. declare @backupSetId as int  
6. select @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‘ )  
7. if @backupSetId is null begin raiserror(N‘驗證失敗。找不到資料庫“AdventureWorks”的備份資訊。‘, 16, 1) end  
8. RESTORE VERIFYONLY FROM  DISK = N‘E:\AdventureWorks.bak‘ WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND  
9. GO  

 

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

 

 

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

 

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

 

執行結果：

 

然後kill掉。

接著繼續備份。

 

然後進行還原，

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

 

 

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

 

 

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

 

 

然後：

 

 

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

 

 

現在再檢查一下：

 

 

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

 

總結：

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

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

這種方法有幾個缺點：

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

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

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

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