SQLServer数据库误操作恢复的方法

前言

在日常开发工作中，有可能会存在数据库的误操作，比如不小心删除了一个表或者删除了一堆数据，这个时候我们就需要将数据库进行恢复，恢复到误操作以前的状态，而这篇文章就是主要如何在误操作之后，恢复数据库的数据。

一、恢复数据实例

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,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事务日志备份，并且设置还原的时间点。最后数据库系统会根据完整数据以及事务日志备份和设置的还原时间点去恢复数据。

总结

参考

SQLServer 2008以上误操作数据库恢复方法——日志尾部备份
数据库误操作恢复实现原理