SQLServer數據庫誤操作恢復的方法
目錄
- 前言
- 一、恢復數據實例
- 1.創建初始數據
- 2.保證數據恢復的前提條件
- 3.模擬不小心誤操作
- 4.開始恢復
- 二、恢復數據原理
- 1.數據庫文件類型
- 2.使用bak恢復數據原理
- 總結
- 參考
前言
在日常開發工作中，有可能會存在數據庫的誤操作，比如不小心刪除了一個表或者刪除了一堆數據，這個時候我們就需要將數據庫進行恢復，恢復到誤操作以前的狀態，而這篇文章就是主要如何在誤操作之后，恢復數據庫的數據。
一、恢復數據實例
1.創建初始數據
use masterif exists (select * from sys.databases where name="test") drop database test;create database test;use testcreate table UserInfo(id int,name varchar(50),nickname varchar(50),);goinsert into UserInfo values (1,"張三","小張");insert into UserInfo values (2,"張三2","小張");insert into UserInfo values (3,"李四","小李");insert into UserInfo values (4,"王五","小王");insert into UserInfo values (5,"趙六","小趙");select * from UserInfo;
2.保證數據恢復的前提條件
前提1 - 數據庫創建時便已設置恢復模式為完整
對于任何環境下，新建數據庫后，我們都需要先檢查數據庫的恢復模式，確認數據庫的恢復模式是【完整恢復模式】，這是數據庫數據得以恢復的基礎。如果等到出問題，再想到這個就為時已晚了。
修改恢復模式如下圖所示：
選中數據庫，然后右鍵菜單=》選擇【屬性】=》屬性頁選擇【選項】=》恢復模式哪一項，選擇【完整】=》點擊【確定】
數據庫一般默認恢復模式就是【完整】恢復模式。
腳本檢查恢復模式：SELECT recovery_model,recovery_model_desc FROM sys.databases WHERE name ='test';
如果查詢結果如下，則表示數據恢復模式配置沒有問題。
前提2 - 至少做過一次完整的備份
數據庫數據恢復是需要基于數據的完整備份上進行的，如果沒有一次完整的數據備份，那么數據備份無從談起，所以，創建完一次數據庫后，需強制性做一次完整的備份。
數據完整備份操作如下圖所示：
選中數據庫=》右鍵菜單，選擇【任務】=》選擇【備份】=》備份類型，選擇【完整】=》確定
擴展內容：可以通過一下sql查詢所有的數據庫備份信息
SELECT database_name,recovery_model,name, backup_finish_date,type FROM msdb.dbo.backupset
如：本人剛才操作了兩次數據完整備份和一次事務日志備份，查詢結果如下，
type為D表示數據備份，L表示事務日志備份：
3.模擬不小心誤操作
再次強調：數據恢復的前提條件中修改備份模式以及強制做一次完整的數據和事務日志備份，是我們在出現問題之前必須做過的操作，否則就沒有什么恢復可言了。
現在我們模擬做一下誤操作：
--模擬誤操作 2022-11-23 14.25 開始的誤操作，記一下誤操作事件，以便后續演示數據恢復--本意刪除張三2的，但是條件不當，將不該刪除的數據也刪除了delete from UserInfo where nickname="小張";insert into UserInfo values (110,"錯誤數據","小錯誤");--不小心新建了一個表格select * into UserInfo2 from UserInfo;
現在我們比對一下誤操作前后數據
4.開始恢復
出現誤操作之后，具體恢復步驟如下：
1、首先將數據庫限制為單個用戶訪問
出現誤操作后，我們需要切斷其他用戶對數據庫在進行操作產生數據，造成更大的問題，因此需要先將數據庫限制為單個用戶訪問，與外界隔絕開。
具體操作如下：
當我們將限制訪問設置為【SINGLE_USER】后，我們會發現 數據庫后面會附加上【單個用戶】標志。
2、做一次事務日志備份（備份日志尾部）
- 首先選擇數據庫，右鍵菜單，選擇【任務】=》選擇【備份】
- 進入備份界面，
- 在【常規】這一項設置中 ，將備份類型選擇【事務日志】
- 在【介質選項】這一項設置中，按照下圖操作步驟進行勾選和操作
- 最后點擊【確定】，此時我們會發現數據庫出現了【正在還原…】的標志
- 具體操作步驟如下圖所示：
如果無法備份，則查詢一下誰在占用：
SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID("test")然后使用kill 語句將占用者清除即可，如 kill 56，56代表占用者的spid
完成以上操作后，繼續進行備份。
3、還原完整數據備份
完成上述事務日志的備份后，我們就需要將數據進行還原。
- 首先選擇數據庫，右鍵菜單=》選擇【任務】=》選擇【還原】=》選擇【文件和文件組】
- 進入【還原文件和文件組】界面
- 【常規】這一項中 在備份集列表中 選擇 文件類型為【行數據的】并且類型為【完整】，備份的完成時間距離誤操作最近的一次備份。
- 【選項】這一項中 選擇【不對數據庫執行任何操作，不回滾未提交的事務】這一項
- 最后點擊【確定】即可
- 具體操作如下圖所示：
4、還原事務日志備份
還原完完整數據備份后，我們需要還原事務日志，因為數據庫最終需要根據事務日志與完整備份的數據進行比對后進行數據的恢復操作。
- 首先選擇數據庫，右鍵菜單=》選擇【任務】=》選擇【還原】=》選擇【事務日志】
- 進入還原事務日志界面
- 【常規】這一項設置中，選擇需要還原的事務日志備份（選擇誤操作后備份的那個事務日志）
- 然后點擊時間點后面的按鈕，打開時間還原窗口，設置需要還原的時間點
- 設置完還原的時間點后，在【選項】這一項設置中，設置恢復狀態為回滾未提供事務，使用數據庫處于可以使用的狀態
- 最后點擊【確定】即可
- 具體操作如下圖所示：
至此，數據已經恢復成功。
5、最后恢復數據庫的限制訪問設置
將限制訪問，從【SINGLE_USER】修改為【MULTI_USER】即可
到此這個數據恢復的完整流程結束。
二、恢復數據原理
1.數據庫文件類型
數據庫中的文件類型：
- 以上有mdf,ndf,ldf 以及bak 四種文件類型，其中一般我們創建的數據庫都會包含mdf 和ldf 兩個文件，不需要手動創建，這兩個是一套數據，可以通過分離的方式拷貝出來作為備份，還原的時候通過數據庫的“附加”功能即可還原。
- bak自成一套，bak文件是通過使用數據庫的“備份”功能而備份出來的文件，里面包含了數據和日志文件，并且備份時做了壓縮，所以同一個數據庫的bak文件比在運行的mdf數據文件＋ldf日志文件要小。
2.使用bak恢復數據原理
首先我們要了解 事務日志中，記錄了數據庫自創建之初數據庫所有的操作日志。
例如：
- 2022-10-10 創建了數據庫，此時我們第一次做了完整備份bak（包含數據+日志）
- 2022-10-11 我們又做了一次完整備份bak（包含數據+日志）
- 2022-10-12
- 12：00 做了誤操作，此時還不知曉，
- 12：05 我們發現自己誤操作了，馬上設置訪問權限，進行事務日志備份（這很關鍵），此時我們就有了2022-10-11所有的完整數據，以及到2022-10-12 12：05所有的事務日志。那么我們就可以根據2022-10-11的完整數據以及2022-10-12 12：05 的事務日志去反推12：00的數據。因此我們需要做的第一個操作先還原2022-10-11的完整數據備份，然后還原2022-10-12 12：05事務日志備份，并且設置還原的時間點。最后數據庫系統會根據完整數據以及事務日志備份和設置的還原時間點去恢復數據。
總結
到此這篇關于SQLServer數據庫誤操作恢復的方法的文章就介紹到這了,更多相關SQLServer數據庫恢復內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
網公網安備