前言

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

一、模擬誤刪

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

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

SSMS 連接本地 SQL Server。

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

創(chuàng)建數(shù)據(jù)庫 “Test”，并在該庫內創(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());

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

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

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

全量備份流程：

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

備份成功。

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

3. 未備份的新操作

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

-- 插入五條測試數(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());

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

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

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

-- 模擬全量更新操作，導致手機號碼丟失
UPDATE Student
SET phone = NULL;

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

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

這是數(shù)據(jù)維護中經常遇到的問題，因為某些原因導致 Where 條件的子項查詢沒有生效，導致全量更新，等同于某一列被直接刪除。還有一些 Delete From / Drop Table 之類的情況，其實和這個的恢復方式一樣。

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

1. 備份日志

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

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

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

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

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

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

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

在通用里，選擇一個還原到的具體時間點。這里的時間點如果記不清了，則需要我們去盡可能推算，因為生產數(shù)據(jù)時刻在變化，盡可能恢復到誤刪前的前一刻的數(shù)據(jù)可以避免更多的損失。

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

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

點擊“確定”開始還原。

還原成功。

3. 檢驗恢復結果

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

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

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

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

從剛剛的截圖上我們看到，雖然數(shù)據(jù)被恢復了，但是因為使用了日志事務，所以Test數(shù)據(jù)庫變成了StandBy/ReadOnly狀態(tài)。當前狀態(tài)下，數(shù)據(jù)庫是無法被寫入的，我們需要解除這種狀態(tài)。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最新評論