前言
在日常的数据库管理中，数据的误删操作是难以避免的。为了确保数据的安全性和完整性，我们必须采取一些措施来进行数据的备份和恢复。本文将详细介绍如何在 SQL Server 中进行数据的备份和恢复操作，特别是在发生数据误删的情况下。假设我们已经开启了全量备份，并且在误操作之前有一个全量备份文件。
一、模拟误删
1. 创建表并插入测试数据
首先，我们需要创建一个名为 “Test” 的数据库，并在其中创建一个名为 “Student” 的表。该表将包含一些测试数据。
SSMS 连接本地 SQL Server。
创建新数据库 “Test”。
创建数据库 “Test”，并在该库内创建数据表 “Student”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | -- 创建数据库CREATE DATABASE Test;-- 使用 Test 数据库USE Test;-- 创建 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());-- 插入十条测试数据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. 数据库全量备份
恢复的前提是数据库在误删前进行过一次全量备份。
全量备份流程：
右键 “Test” 数据库，点击备份(Back Up)，备份文件命名为 “testDB.bak”，存储在自定义目录，我存储在 “D:testDB.bak”。
备份成功。
记录本次操作时间为：2024-07-23 17:32:30
3. 未备份的新操作
如果我们的数据库全量备份之后没有任何操作，那这个还原是毫无难度的，草履虫也会。本篇重点讲如果全量备份之后，再有为备份的新操作该如何处理，这也符合实际应用中的场景。
1 2 3 4 5 6 7 | -- 插入五条测试数据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. 模拟数据误删
为了模拟数据误删的情况，我们将进行一次全量更新操作，导致所有记录的手机号码（phone）字段丢失。
1 2 3 | -- 模拟全量更新操作，导致手机号码丢失UPDATE StudentSET phone = NULL; |
执行上述 SQL 脚本后，Student 表中的所有记录的 phone 字段将被更新为 NULL，模拟了数据误删的情况。
记录本次操作时间为：2024-07-23 17:35:41
这是数据维护中经常遇到的问题，因为某些原因导致 Where 条件的子项查询没有生效，导致全量更新，等同于某一列被直接删除。还有一些 Delete From / Drop Table 之类的情况，其实和这个的恢复方式一样。
二、数据恢复步骤
1. 备份日志
在误删发生后，我们需要备份当前的事务日志，以确保在恢复过程中不会丢失任何数据。
1 2 3 | -- 备份当前的事务日志BACKUP LOG Test TO DISK='d:testLOG.bak' WITH FORMATGO |
记录本次操作时间为：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 2 3 4 5 6 7 | -- 查看 Student 表中的数据SELECT TOP (1000) [id] ,[name] ,[phone] ,[gender] ,[created_at] FROM [Test].[dbo].[Student] |
执行上述查询语句后，我们可以看到所有记录的 phone 字段已经被恢复到误操作之前的状态。
4. 恢复数据库可读写
从刚刚的截图上我们看到，虽然数据被恢复了，但是因为使用了日志事务，所以Test数据库变成了StandBy/ReadOnly状态。当前状态下，数据库是无法被写入的，我们需要解除这种状态。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- 切换到 master 数据库USE master;-- 在主服务器上移除日志传送配置EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = 'Test', -- 主数据库名称 @secondary_server = '', -- 备用服务器名称 @secondary_database = 'Test'; -- 备用数据库名称-- 在主服务器上移除主数据库的日志传送配置EXEC master.dbo.sp_delete_log_shipping_primary_database @database = 'Test'; -- 主数据库名称-- 在备用服务器上移除日志传送配置EXEC master.dbo.sp_delete_log_shipping_secondary_database @secondary_database = 'Test'; -- 备用数据库名称-- 恢复数据库RESTORE DATABASE Test WITH RECOVERY;-- 将数据库设置为读写模式ALTER DATABASE Test SET READ_WRITE; |
我们刷新数据库，看到数据库Test已经变为可写入的正常状态了。
三、SQL Server 数据误删总结
通过本文的介绍，我们学习了如何在 SQL Server 中进行数据的备份和恢复操作，特别是在数据误删的情况下。数据误删是数据库管理中一个常见而严重的问题，如果没有有效的备份和恢复策略，可能会导致无法挽回的损失。
1. 数据备份策略
定期备份是保障数据安全的最有效手段之一。SQL Server 提供了多种备份策略，包括：
- 完全备份：备份整个数据库的所有数据。这种备份方式最为全面，但也最耗时和占用空间最多。
- 差异备份：备份自上次完全备份以来所有更改的数据。它比完全备份更快，但仍然需要上次的完全备份来恢复数据。
- 事务日志备份：备份自上次事务日志备份以来所有更改的事务日志。它允许我们恢复到特定的时间点，非常适合用于数据误删后的恢复。
2. 数据恢复操作
当数据被误删时，正确的恢复操作至关重要。通过以下步骤，我们可以有效地恢复数据：
- 识别误删数据的时间点：确定数据被误删的具体时间。
- 停止数据库的写操作：防止新的数据写入干扰恢复过程。
- 还原最近的完全备份：
1 | RESTORE DATABASE [YourDatabase] FROM DISK = 'C:BackupYourDatabase_full.bak' WITH NORECOVERY; |
还原最近的差异备份（如果有）：
1 | RESTORE DATABASE [YourDatabase] FROM DISK = 'C:BackupYourDatabase_diff.bak' WITH NORECOVERY; |
还原事务日志备份，直到误删数据的时间点：
1 | RESTORE LOG [YourDatabase] FROM DISK = 'C:BackupYourDatabase_log.trn' WITH STOPAT = 'YYYY-MM-DDTHH:MM:SS', RECOVERY; |
通过全量备份和事务日志备份，我们能够有效地恢复误删的数据，确保数据的完整性和安全性。希望本文对大家在日常的数据库管理工作中有所帮助。
以上就是SQL Server数据误删的恢复和备份流程的详细内容，更多关于SQL Server数据恢复和备份的资料请关注IT俱乐部其它相关文章！
