SQL Server 数据误删的规复

在日常的数据库管理中，数据的误删操作是难以制止的。为了确保数据的安全性和完整性，我们必须采取一些措施来进行数据的备份和规复。本文将详细介绍怎样在 SQL Server 中进行数据的备份和规复操作，特别是在发生数据误删的情况下。假设我们已经开启了全量备份，而且在误操作之前有一个全量备份文件。

---

一、模仿误删

1. 创建表并插入测试数据

起首，我们需要创建一个名为 “Test” 的数据库，并在此中创建一个名为 “Student” 的表。该表将包罗一些测试数据。
SSMS 毗连当地 SQL Server。

创建新数据库 “Test”。

创建数据库 “Test”，并在该库内创建数据表 “Student”

1. -- 创建数据库
2. CREATE DATABASE Test;
4. -- 使用 Test 数据库
5. USE Test;
7. -- 创建 Student 表
8. CREATE TABLE Student (
9.     id INT IDENTITY(1,1) PRIMARY KEY,
10.     name NVARCHAR(255) NOT NULL,
11.     phone NVARCHAR(50) NOT NULL,
12.     gender NVARCHAR(10) NOT NULL,
13.     created_at DATETIME DEFAULT GETDATE()
14. );
16. -- 插入十条测试数据
17. INSERT INTO Student (name, phone, gender, created_at) VALUES
18. ('Alice', '1234567890', 'Female', GETDATE()),
19. ('Bob', '0987654321', 'Male', GETDATE()),
20. ('Cathy', '1231231234', 'Female', GETDATE()),
21. ('David', '3213214321', 'Male', GETDATE()),
22. ('Eva', '5556667777', 'Female', GETDATE()),
23. ('Frank', '8889990000', 'Male', GETDATE()),
24. ('Grace', '2223334444', 'Female', GETDATE()),
25. ('Henry', '4445556666', 'Male', GETDATE()),
26. ('Ivy', '1112223333', 'Female', GETDATE()),
27. ('Jack', '7778889999', 'Male', GETDATE());

复制代码

记录本次操作时间为：2024-07-23 17:30:45

---

2. 数据库全量备份

规复的前提是数据库在误删前进行过一次全量备份。
全量备份流程：
右键 “Test” 数据库，点击备份(Back Up)，备份文件命名为 “testDB.bak”，存储在自界说目录，我存储在 “D:\testDB.bak”。

备份成功。

记录本次操作时间为：2024-07-23 17:32:30

---

3. 未备份的新操作

假如我们的数据库全量备份之后没有任何操作，那这个还原是毫无难度的，草履虫也会。本篇重点讲假如全量备份之后，再有为备份的新操作该怎样处理，这也符合实际应用中的场景。

1. -- 插入五条测试数据
2. INSERT INTO Student (name, phone, gender, created_at) VALUES
3. ('Lily', '1114447777', 'Female', GETDATE()),
4. ('Mike', '2225558888', 'Male', GETDATE()),
5. ('Nina', '3336669999', 'Female', GETDATE()),
6. ('Oscar', '4447770000', 'Male', GETDATE()),
7. ('Paul', '5558881111', 'Male', GETDATE());

复制代码

记录本次操作时间为：2024-07-23 17:35:14

---

4. 模仿数据误删

为了模仿数据误删的情况，我们将进行一次全量更新操作，导致全部记录的手机号码（phone）字段丢失。

1. -- 模拟全量更新操作，导致手机号码丢失
2. UPDATE Student
3. SET phone = NULL;

复制代码

执行上述 SQL 脚本后，Student 表中的全部记录的 phone 字段将被更新为 NULL，模仿了数据误删的情况。

记录本次操作时间为：2024-07-23 17:35:41
这是数据维护中常常遇到的问题，由于某些原因导致 Where 条件的子项查询没有生效，导致全量更新，等同于某一列被直接删除。另有一些 Delete From / Drop Table 之类的情况，其实和这个的规复方式一样。

---

二、数据规复步调

1. 备份日记

在误删发生后，我们需要备份当前的事务日记，以确保在规复过程中不会丢失任何数据。

1. -- 备份当前的事务日志
2. BACKUP LOG Test TO DISK='d:\testLOG.bak' WITH FORMAT
3. GO

复制代码

记录本次操作时间为：2024-07-23 17:37:16

---

2. 还原数据库到指定时间点

接下来，我们将还原数据库到误操作之前的状态。这个过程包括还原之前的全量备份和刚刚备份的事务日记（截至到误删前的部分）。
在 SQL Server Management Studio 中，右键单击要还原的数据库（Test），选择“使命” -> “还原” -> “数据库”
在“选项”标签中，勾选“关闭现有毗连到目标数据库”，选择 之前的全量备份 和 刚刚备份的事务日记。

在通用里，选择一个还原到的具体时间点。这里的时间点假如记不清了，则需要我们去尽可能推算，由于生产数据时间在厘革，尽可能规复到误删前的前一刻的数据可以制止更多的丧失。

这里我们的误删操作发生在：2024-07-23 17:35:41。
因此，我们选择还原到这个时间的前一刻，我选择 2024-07-23 17:35:30。

点击“确定”开始还原。

还原成功。

---

3. 检验规复结果

还原完成后，我们需要验证数据是否已经被成功规复。

1. -- 查看 Student 表中的数据
2. SELECT TOP (1000) [id]
3.       ,[name]
4.       ,[phone]
5.       ,[gender]
6.       ,[created_at]
7.   FROM [Test].[dbo].[Student]

复制代码

执行上述查询语句后，我们可以看到全部记录的 phone 字段已经被规复到误操作之前的状态。

---

4. 规复数据库可读写

从刚刚的截图上我们看到，固然数据被规复了，但是由于使用了日记事务，所以Test数据库酿成了StandBy/ReadOnly状态。当前状态下，数据库是无法被写入的，我们需要解除这种状态。

1. -- 切换到 master 数据库
2. USE master;
4. -- 在主服务器上移除日志传送配置
5. EXEC master.dbo.sp_delete_log_shipping_primary_secondary
6.     @primary_database = 'Test',            -- 主数据库名称
7.     @secondary_server = '<SecondaryServerName>', -- 备用服务器名称
8.     @secondary_database = 'Test';          -- 备用数据库名称
10. -- 在主服务器上移除主数据库的日志传送配置
11. EXEC master.dbo.sp_delete_log_shipping_primary_database
12.     @database = 'Test';                    -- 主数据库名称
14. -- 在备用服务器上移除日志传送配置
15. EXEC master.dbo.sp_delete_log_shipping_secondary_database
16.     @secondary_database = 'Test';          -- 备用数据库名称
18. -- 恢复数据库
19. RESTORE DATABASE Test WITH RECOVERY;
21. -- 将数据库设置为读写模式
22. ALTER DATABASE Test SET READ_WRITE;

复制代码

我们刷新数据库，看到数据库Test已经变为可写入的正常状态了。

---

三、SQL Server 数据误删总结

通过本文的介绍，我们学习了怎样在 SQL Server 中进行数据的备份和规复操作，特别是在数据误删的情况下。数据误删是数据库管理中一个常见而严重的问题，假如没有有效的备份和规复策略，可能会导致无法挽回的丧失。
1. 数据备份策略

定期备份是保障数据安全的最有效手段之一。SQL Server 提供了多种备份策略，包括：

• 完全备份：备份整个数据库的全部数据。这种备份方式最为全面，但也最耗时和占用空间最多。
  
• 差异备份：备份自上次完全备份以来全部更改的数据。它比完全备份更快，但仍然需要上次的完全备份来规复数据。
  
• 事务日记备份：备份自上次事务日记备份以来全部更改的事务日记。它答应我们规复到特定的时间点，非常得当用于数据误删后的规复。
  

---

2. 数据规复操作

当数据被误删时，正确的规复操作至关重要。通过以下步调，我们可以有效地规复数据：

• 识别误删数据的时间点：确定命据被误删的具体时间。
  
• 停止数据库的写操作：防止新的数据写入干扰规复过程。
  
• 还原最近的完全备份：
  1. RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_full.bak' WITH NORECOVERY;

  复制代码
• 还原最近的差异备份（假如有）：
  1. RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_diff.bak' WITH NORECOVERY;

  复制代码
• 还原事务日记备份，直到误删数据的时间点：
  1. RESTORE LOG [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase_log.trn' WITH STOPAT = 'YYYY-MM-DDTHH:MM:SS', RECOVERY;

  复制代码

通过全量备份和事务日记备份，我们可以或许有效地规复误删的数据，确保数据的完整性和安全性。希望本文对各人在日常的数据库管理工作中有所帮助。

---

3. 本文参考资料

• SQL Server 官方文档
  
• SQL Server Management Studio 使用手册
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。