前言

在日常的數(shù)據(jù)庫管理中，數(shù)據(jù)的誤刪操作是難以避免的。為了確保數(shù)據(jù)的安全性和完整性，我們必須采取一些措施來進(jìn)行數(shù)據(jù)的備份和恢復(fù)。本文將詳細(xì)介紹如何在 SQL Server 中進(jìn)行數(shù)據(jù)的備份和恢復(fù)操作，特別是在發(fā)生數(shù)據(jù)誤刪的情況下。假設(shè)我們已經(jīng)開啟了全量備份，并且在誤操作之前有一個(gè)全量備份文件。

一、模擬誤刪

1. 創(chuàng)建表并插入測試數(shù)據(jù)

首先，我們需要?jiǎng)?chuàng)建一個(gè)名為 “Test” 的數(shù)據(jù)庫，并在其中創(chuàng)建一個(gè)名為 “Student” 的表。該表將包含一些測試數(shù)據(jù)。

SSMS 連接本地 SQL Server。

創(chuàng)建新數(shù)據(jù)庫 “Test”。

創(chuàng)建數(shù)據(jù)庫 “Test”，并在該庫內(nèi)創(chuàng)建數(shù)據(jù)表 “Student”

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE Test;

-- 使用 Test 數(shù)據(jù)庫
USE Test;

-- 創(chuàng)建 Student 表
CREATE TABLE Student (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    phone NVARCHAR(50) NOT NULL,
    gender NVARCHAR(10) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
);

-- 插入十條測試數(shù)據(jù)
INSERT INTO Student (name, phone, gender, created_at) VALUES 
('Alice', '1234567890', 'Female', GETDATE()),
('Bob', '0987654321', 'Male', GETDATE()),
('Cathy', '1231231234', 'Female', GETDATE()),
('David', '3213214321', 'Male', GETDATE()),
('Eva', '5556667777', 'Female', GETDATE()),
('Frank', '8889990000', 'Male', GETDATE()),
('Grace', '2223334444', 'Female', GETDATE()),
('Henry', '4445556666', 'Male', GETDATE()),
('Ivy', '1112223333', 'Female', GETDATE()),
('Jack', '7778889999', 'Male', GETDATE());

記錄本次操作時(shí)間為：2024-07-23 17:30:45

2. 數(shù)據(jù)庫全量備份

恢復(fù)的前提是數(shù)據(jù)庫在誤刪前進(jìn)行過一次全量備份。

全量備份流程：

右鍵 “Test” 數(shù)據(jù)庫，點(diǎn)擊備份(Back Up)，備份文件命名為 “testDB.bak”，存儲(chǔ)在自定義目錄，我存儲(chǔ)在 “D:\testDB.bak”。

備份成功。

記錄本次操作時(shí)間為：2024-07-23 17:32:30

3. 未備份的新操作

如果我們的數(shù)據(jù)庫全量備份之后沒有任何操作，那這個(gè)還原是毫無難度的，草履蟲也會(huì)。本篇重點(diǎn)講如果全量備份之后，再有為備份的新操作該如何處理，這也符合實(shí)際應(yīng)用中的場景。

-- 插入五條測試數(shù)據(jù)
INSERT INTO Student (name, phone, gender, created_at) VALUES 
('Lily', '1114447777', 'Female', GETDATE()),
('Mike', '2225558888', 'Male', GETDATE()),
('Nina', '3336669999', 'Female', GETDATE()),
('Oscar', '4447770000', 'Male', GETDATE()),
('Paul', '5558881111', 'Male', GETDATE());

記錄本次操作時(shí)間為：2024-07-23 17:35:14

4. 模擬數(shù)據(jù)誤刪

為了模擬數(shù)據(jù)誤刪的情況，我們將進(jìn)行一次全量更新操作，導(dǎo)致所有記錄的手機(jī)號(hào)碼（phone）字段丟失。

-- 模擬全量更新操作，導(dǎo)致手機(jī)號(hào)碼丟失
UPDATE Student
SET phone = NULL;

執(zhí)行上述 SQL 腳本后，Student 表中的所有記錄的 phone 字段將被更新為 NULL，模擬了數(shù)據(jù)誤刪的情況。

記錄本次操作時(shí)間為：2024-07-23 17:35:41

這是數(shù)據(jù)維護(hù)中經(jīng)常遇到的問題，因?yàn)槟承┰驅(qū)е?Where 條件的子項(xiàng)查詢沒有生效，導(dǎo)致全量更新，等同于某一列被直接刪除。還有一些 Delete From / Drop Table 之類的情況，其實(shí)和這個(gè)的恢復(fù)方式一樣。

二、數(shù)據(jù)恢復(fù)步驟

1. 備份日志

在誤刪發(fā)生后，我們需要備份當(dāng)前的事務(wù)日志，以確保在恢復(fù)過程中不會(huì)丟失任何數(shù)據(jù)。

-- 備份當(dāng)前的事務(wù)日志
BACKUP LOG Test TO DISK='d:\testLOG.bak' WITH FORMAT
GO

記錄本次操作時(shí)間為：2024-07-23 17:37:16

2. 還原數(shù)據(jù)庫到指定時(shí)間點(diǎn)

接下來，我們將還原數(shù)據(jù)庫到誤操作之前的狀態(tài)。這個(gè)過程包括還原之前的全量備份和剛剛備份的事務(wù)日志（截至到誤刪前的部分）。

在 SQL Server Management Studio 中，右鍵單擊要還原的數(shù)據(jù)庫（Test），選擇“任務(wù)” -> “還原” -> “數(shù)據(jù)庫”

在“選項(xiàng)”標(biāo)簽中，勾選“關(guān)閉現(xiàn)有連接到目標(biāo)數(shù)據(jù)庫”，選擇 之前的全量備份 和 剛剛備份的事務(wù)日志。

在通用里，選擇一個(gè)還原到的具體時(shí)間點(diǎn)。這里的時(shí)間點(diǎn)如果記不清了，則需要我們?nèi)ケM可能推算，因?yàn)樯a(chǎn)數(shù)據(jù)時(shí)刻在變化，盡可能恢復(fù)到誤刪前的前一刻的數(shù)據(jù)可以避免更多的損失。

這里我們的誤刪操作發(fā)生在：2024-07-23 17:35:41。

因此，我們選擇還原到這個(gè)時(shí)間的前一刻，我選擇 2024-07-23 17:35:30。

點(diǎn)擊“確定”開始還原。

還原成功。

3. 檢驗(yàn)恢復(fù)結(jié)果

還原完成后，我們需要驗(yàn)證數(shù)據(jù)是否已經(jīng)被成功恢復(fù)。

-- 查看 Student 表中的數(shù)據(jù)
SELECT TOP (1000) [id]
      ,[name]
      ,[phone]
      ,[gender]
      ,[created_at]
  FROM [Test].[dbo].[Student]

執(zhí)行上述查詢語句后，我們可以看到所有記錄的 phone 字段已經(jīng)被恢復(fù)到誤操作之前的狀態(tài)。

4. 恢復(fù)數(shù)據(jù)庫可讀寫

從剛剛的截圖上我們看到，雖然數(shù)據(jù)被恢復(fù)了，但是因?yàn)槭褂昧巳罩臼聞?wù)，所以Test數(shù)據(jù)庫變成了StandBy/ReadOnly狀態(tài)。當(dāng)前狀態(tài)下，數(shù)據(jù)庫是無法被寫入的，我們需要解除這種狀態(tài)。

-- 切換到 master 數(shù)據(jù)庫
USE master;

-- 在主服務(wù)器上移除日志傳送配置
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = 'Test',            -- 主數(shù)據(jù)庫名稱
    @secondary_server = '<SecondaryServerName>', -- 備用服務(wù)器名稱
    @secondary_database = 'Test';          -- 備用數(shù)據(jù)庫名稱

-- 在主服務(wù)器上移除主數(shù)據(jù)庫的日志傳送配置
EXEC master.dbo.sp_delete_log_shipping_primary_database
    @database = 'Test';                    -- 主數(shù)據(jù)庫名稱

-- 在備用服務(wù)器上移除日志傳送配置
EXEC master.dbo.sp_delete_log_shipping_secondary_database
    @secondary_database = 'Test';          -- 備用數(shù)據(jù)庫名稱

-- 恢復(fù)數(shù)據(jù)庫
RESTORE DATABASE Test WITH RECOVERY;

-- 將數(shù)據(jù)庫設(shè)置為讀寫模式
ALTER DATABASE Test SET READ_WRITE;

我們刷新數(shù)據(jù)庫，看到數(shù)據(jù)庫Test已經(jīng)變?yōu)榭蓪懭氲恼顟B(tài)了。

三、SQL Server 數(shù)據(jù)誤刪總結(jié)

通過本文的介紹，我們學(xué)習(xí)了如何在 SQL Server 中進(jìn)行數(shù)據(jù)的備份和恢復(fù)操作，特別是在數(shù)據(jù)誤刪的情況下。數(shù)據(jù)誤刪是數(shù)據(jù)庫管理中一個(gè)常見而嚴(yán)重的問題，如果沒有有效的備份和恢復(fù)策略，可能會(huì)導(dǎo)致無法挽回的損失。

1. 數(shù)據(jù)備份策略

定期備份是保障數(shù)據(jù)安全的最有效手段之一。SQL Server 提供了多種備份策略，包括：

• 完全備份：備份整個(gè)數(shù)據(jù)庫的所有數(shù)據(jù)。這種備份方式最為全面，但也最耗時(shí)和占用空間最多。
• 差異備份：備份自上次完全備份以來所有更改的數(shù)據(jù)。它比完全備份更快，但仍然需要上次的完全備份來恢復(fù)數(shù)據(jù)。
• 事務(wù)日志備份：備份自上次事務(wù)日志備份以來所有更改的事務(wù)日志。它允許我們恢復(fù)到特定的時(shí)間點(diǎn)，非常適合用于數(shù)據(jù)誤刪后的恢復(fù)。

2. 數(shù)據(jù)恢復(fù)操作

當(dāng)數(shù)據(jù)被誤刪時(shí)，正確的恢復(fù)操作至關(guān)重要。通過以下步驟，我們可以有效地恢復(fù)數(shù)據(jù)：

• 識(shí)別誤刪數(shù)據(jù)的時(shí)間點(diǎn)：確定數(shù)據(jù)被誤刪的具體時(shí)間。
• 停止數(shù)據(jù)庫的寫操作：防止新的數(shù)據(jù)寫入干擾恢復(fù)過程。
• 還原最近的完全備份：

RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_full.bak' WITH NORECOVERY;

還原最近的差異備份（如果有）：

RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_diff.bak' WITH NORECOVERY;

還原事務(wù)日志備份，直到誤刪數(shù)據(jù)的時(shí)間點(diǎn)：

RESTORE LOG [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_log.trn' WITH STOPAT = 'YYYY-MM-DDTHH:MM:SS', RECOVERY;

通過全量備份和事務(wù)日志備份，我們能夠有效地恢復(fù)誤刪的數(shù)據(jù)，確保數(shù)據(jù)的完整性和安全性。希望本文對(duì)大家在日常的數(shù)據(jù)庫管理工作中有所幫助。

以上就是SQL Server數(shù)據(jù)誤刪的恢復(fù)和備份流程的詳細(xì)內(nèi)容，更多關(guān)于SQL Server數(shù)據(jù)恢復(fù)和備份的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！