SQL Server 2008以上誤操作數(shù)據(jù)庫恢復(fù)方法——日志尾部備份

時間：2018-12-06 10:24:11

關(guān)鍵字： server sql 數(shù)庫庫恢復(fù)

手機看文章

掃描二維碼
隨時隨地手機看文章

[導(dǎo)讀]問題：? ? ? ? ?經(jīng)?？吹接腥苏`刪數(shù)據(jù)，或者誤操作，特別是update和delete的時候沒有加where，然后就喊爹喊娘了。人非圣賢孰能無過，做錯可以理解，但不能縱容，這個以后再說，現(xiàn)在先來解

問題：

? ? ? ? ?經(jīng)?？吹接腥苏`刪數(shù)據(jù)，或者誤操作，特別是update和delete的時候沒有加where，然后就喊爹喊娘了。人非圣賢孰能無過，做錯可以理解，但不能縱容，這個以后再說，現(xiàn)在先來解決問題。

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

解決方法：

? ? ? ?對于這類問題，主要是找回誤操作之前的數(shù)據(jù)，在2008之前，有個很出名的工具Log Exploer，聽說還挺好用的，這個網(wǎng)上大把教程，這里就不多說了。但是唯一遺憾的是，不支持2008及更高版本，這時除了其他第三方工具，那么最常用的就是本文提到的方法——日志尾部備份。本文實驗環(huán)境2008R2，對于2008及其以上版本可以使用這個方法，其實2005也可以，2000很少用，沒試過，只是2008之前可以使用Log Exploer，所以就沒必要用這種方法。

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

步驟：（1）、檢查數(shù)據(jù)庫的恢復(fù)模式，如圖：

或者使用腳本檢查：

[sql]?view plain?copy SELECT?recovery_model,recovery_model_desc?? FROM?sys.databases?? WHERE?name?='AdventureWorks'??

結(jié)果如下：

? ? ? ? 確保數(shù)據(jù)庫的恢復(fù)模式最起碼不能為【簡單】。至于如何修改成完整模式，我覺得這些應(yīng)該沒必要多說了。

? ? ? ?切記，對于任何重要環(huán)境，不僅僅是客戶正式環(huán)境（俗稱生產(chǎn)環(huán)境），都強烈建議使用【完整恢復(fù)模式】，雖然對于另外兩種（大容量日志（BULK_LOGGED）、簡單（SIMPLE））來說，完整恢復(fù)模式產(chǎn)生的日志會大，但是在出現(xiàn)問題的時候，就會覺得這些都不算什么了。并且我也想不到任何理由對于正式環(huán)境不使用完整恢復(fù)模式。只要管理得當(dāng)，完整恢復(fù)模式的日志也不會太變態(tài)。

（2）、這里其實隱含另外一步，曾經(jīng)做過最少一次的完整備份。因為所有類型的備份都基于完整備份，如果沒有最少一次完整備份，其他類型的備份都是多余的，所以在這里強調(diào)一下，在創(chuàng)建完一個新數(shù)據(jù)庫之后，強烈建議甚至強制做一次完整備份。

[sql]?view plain?copy SELECT??database_name,recovery_model,name??? FROM?msdb.dbo.backupset??

使用上面的語句粗略可以看到有那些數(shù)據(jù)庫做過備份，由于測試，所以做了幾次備份，可以看到我這個時間點已經(jīng)做了備份了。

（3）、確保別人不再連接數(shù)據(jù)庫，然后做一次日志尾部備份：

首先先創(chuàng)建一點數(shù)據(jù)：

[sql]?view plain?copy /*?? 由于tempdb永遠(yuǎn)為簡單恢復(fù)模式，所以不適合做案例。?? 這里使用微軟的示例數(shù)據(jù)庫AdventureWorks?? */?? USE?AdventureWorks?? GO?? IF?OBJECT_ID('testRestore')?IS?NOT?NULL??? ????DROP?TABLE?testRestore?? GO?? CREATE?TABLE?testRestore?? ????(?? ??????id?INT?IDENTITY(1,?1)?,?? ??????NAME?VARCHAR(50)?? ????);?? --插入測試數(shù)據(jù)：????? 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?? 檢查一下結(jié)果：

然后來做個刪除操作，為了定位是啥時候發(fā)生的，我加了一個waitfor命令，讓它在某個時間發(fā)生，這樣恢復(fù)的時候就有準(zhǔn)確性：

[sql]?view plain?copy USE?AdventureWorks?? GO?? WAITFOR?TIME?'21:45'?? DELETE?FROM?dbo.testRestore??

現(xiàn)在來看看數(shù)據(jù)：

[sql]?view plain?copy USE?AdventureWorks?? GO?? SELECT?*?FROM?dbo.testRestore??

到這一步，災(zāi)難出現(xiàn)了。但是切記要冷靜。

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

然后在【選項】頁選擇：除【事務(wù)日志】除，其他紅框包裹的地方為強烈建議勾選的地方。并且保證數(shù)據(jù)庫不要有別人在連接，因為備份日志尾部會使數(shù)據(jù)庫處于還原狀態(tài)，拒絕其他會話的連接，如果不斷開其他連接，是備份不了的。

然后按確定，當(dāng)然，可以使用上方的【腳本】來生成語句：

[sql]?view plain?copy USE?Master?? GO?? BACKUP?LOG?[AdventureWorks]?TO??DISK?=?N'E:AdventureWorks.bak'?WITH??NO_TRUNCATE?,?NOFORMAT,?NOINIT,??NAME?=?N'AdventureWorks-事務(wù)日志?備份',?SKIP,?NOREWIND,?NOUNLOAD,??NORECOVERY?,?COMPRESSION,??STATS?=?10,?CHECKSUM?? GO?? declare?@backupSetId?as?int?? 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'?)?? if?@backupSetId?is?null?begin?raiserror(N'驗證失敗。找不到數(shù)據(jù)庫“AdventureWorks”的備份信息。',?16,?1)?end?? RESTORE?VERIFYONLY?FROM??DISK?=?N'E:AdventureWorks.bak'?WITH??FILE?=?@backupSetId,??NOUNLOAD,??NOREWIND?? GO??

此時，數(shù)據(jù)庫會處于【正在還原】的狀態(tài)

如果發(fā)現(xiàn)備份不了可以用下面語句查看，并把spid殺掉：

[sql]?view plain?copy SELECT??*?FROM?sys.sysprocesses?WHERE?dbid=DB_ID('AdventureWorks')??

執(zhí)行結(jié)果：

然后kill掉。

接著繼續(xù)備份。

然后進(jìn)行還原，如圖：

先要還原完整備份，選擇最近的那次，由于日志備份的特性（以后其他文章再說），只認(rèn)最后一次備份，所以要選擇最新的那次，否則還原不了。

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

接著還原日志文件，這是最最重要的一步：

然后：

由于實驗的時候出了點問題，后面重做了，所以時間選擇到22：19分，我是在22：20分刪除數(shù)據(jù)的。這里不用太在意，只要把時間點指定到你誤刪除的時間之前即可。而由于日志尾部備份都是最后一個備份文件，所以這里選則紅框部分即可：