SQLServer資料庫誤操作恢復的方法

2023-02-01 18:02:33

前言

在日常開發工作中，有可能會存在資料庫的誤操作，比如不小心刪除了一個表或者刪除了一堆資料，這個時候我們就需要將資料庫進行恢復，恢復到誤操作以前的狀態，而這篇文章就是主要如何在誤操作之後，恢復資料庫的資料。

一、恢復資料範例

1.建立初始資料

use master
if exists (select * from sys.databases where name='test') 
drop database test;

create database test;

use test
create table UserInfo
(
id int,
name varchar(50),
nickname varchar(50),
);
go

insert 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 （primary data file）	主要資料檔案，資料庫系統的可實時操作/讀取的資料檔案，也可作為物理備份檔案使用
ndf （secondary data files）	次要資料檔案
ldf （Log data files）	事務紀錄檔檔案，用於記錄資料庫的事務紀錄檔資訊
bak	資料庫備份檔案

以上有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事務紀錄檔備份，並且設定還原的時間點。最後資料庫系統會根據完整資料以及事務紀錄檔備份和設定的還原時間點去恢復資料。