执行delete没加where条件,误删数据该怎么办?

点击关注上方“ SQL数据库开发 ”，

设为“置顶或星标 ”，第一时间送达干货

“救命啊，执行DELETE没加WHERE条件，数据还能找回来吗？” 
  ——来自一位操作失误的小伙伴的求救
常常有小伙伴在微信群里或私聊我，说自己忘了加WHERE条件，不小心DELETE了整个表里的数据，不能回滚的那种，有没有什么办法可以找回？
像Oracle这种不自动提交的是可以直接通过回滚或闪回来找回数据的，但是像SQL Server这种自动提交，其实也可以设置成不自动提交。具体方法如下：
1、打开SQL Server Management Studio（SSMS）管理工具
2、连接数据库后，选择菜单栏的“工具”-“选项”，打开选项窗口。
3、在选项窗口中，找到“查询执行”-“ANSI”，在配置项页面中找到“SET_IMPLICT_TRANSACTIONS(M)”，并将其设置为选中状态，如下图所示， 然后点击“确定”。
4、设置完后，需要重启SSMS管理工具，下次你执行DELETE的时候就不会自动提交了，需要你执行COMMIT命令才会生效。

但是很多新手小伙伴并不知道这回事，或者习惯了自动提交，不小心误删了数据又该如何补救呢？
今天我们就给小伙伴们分享一个补救的办法，亲测有效。

测试背景 我们使用的SQL Server 2017作为操作平台，在数据库AdventureWorks中新建一张表并插入部分数据，然后将这些数据删除，再通过一系列的操作看是否能将这些数据恢复成我们最开始插入的数据。

前期准备 1、恢复模式为【完整】 需要保证数据库的恢复模式为【大容量日志】或【完整】， 不能为【简单】 。具体操作如下：
选中需要还原数据的数据库，右键【属性】——【选项】——将右侧的恢复模式选为【完整】或【大容量日志】(建议选为【完整】)，点击确认即可。如下图1，图2 图1
图2 同时也可以用脚本查看当前数据库的恢复模式进一步确认。

      SELECT recovery_model,recovery_model_desc
FROM sys.databases
WHERE name ='AdventureWorks'
      

结果如图3： 图3 其中的FULL就代表【完整】恢复模式的意思。
2、至少完整备份一次数据库 因为所有类型的备份都基于完整备份，如果没有一次完整备份，其他类型的备份都是多余的，所以在这里强调一下， 在创建完一个新数据库之后，强烈建议甚至做一次完整备份 ，这里我们先演示一次完整备份。
将已打开的查询页面全部关闭 。右键AdventureWorks数据库——【任务】——【备份】，在弹出的对话框中进行配置，如图4： 图4 点击确定即可开始备份，待备份完成弹出备份成功我们就完成了一次完整备份。

注意：如果有用户在使用当前数据库AdventureWorks，则会一直卡在备份界面，所以在操作前请务必将所有查询页面关闭。
备份完成后可以查询刚才备份的文件

      SELECT  database_name,recovery_model,name,backup_finishi_date
FROM msdb.dbo.backupset
      

结果如图5：
图5

建立测试数据 我们在AdventureWorks下新建一个TEST表，并插入一些测试数据。

      USE AdventureWorks
GO
IF OBJECT_ID('TEST') IS NOT NULL 
    DROP TABLE TEST
GO
CREATE TABLE TEST
(
ID VARCHAR(10),
TNAME NVARCHAR(20),
AGE INT
)

INSERT INTO dbo.TEST
VALUES  ('1001',N'张三',20),
  ('1002',N'李四',23),
  ('1003',N'王五',21),
  ('1004',N'马六',22),
  ('1005',N'赵七',20),
  ('1006',N'宋一',19),
  ('1007',N'刘二',22)
      

查看一下表TEST里的内容，如图6：
图6
然后来做个删除操作，为了定位是什么时候发生的，我加了一个WAITFOR 命令，让它在指定的时间执行，这样恢复的时候就有准确性：

      USE AdventureWorks
GO
WAITFOR TIME '16:09'
DELETE FROM dbo.TEST
      

然后再来看TEST表中的数据，如图7，已经“不小心”全部删除了：
图7

备份事务日志 我们用【备份日志尾部】的方法来恢复刚才被删除的数据。 在操作前同样需要关闭当前所有连接 。 具体步骤如下：选中数据库AdventureWorks——右键【任务】——【备份】，在弹出的对话框中将备份类型选为【事务日志】，如图8：
图8 点击左侧菜单栏的【介质选项】，做如图9的配置，在点击确定之前请再次确认是否有用户连接当前数据库，本地用户请将所有使用到该数据库的页面都关闭。 图9 待提示备份完成后，数据库会出现【正在还原...】字样，如图10：
图10
开始还原 首先我们要还原最近完整备份的那个备份文件， 由于日志备份的特性，只认最近一次备份，所以要选择最新的那次备份文件，否则还原不了 。具体操作如下： 右键AdventureWorks——【任务】——【还原】——【数据库】，在弹出的界面中做如图11的配置，只勾选组件为数据库的那个备份文件。 图11 点击左侧菜单栏【选项】做图12的配置，将恢复状态改完【RESTORE WITH NORECOVERY】：
图12 点击确定后待数据库还原成功即可进行下一步操作。
刚才我们已经还原了数据文件，下面我们继续还原日志文件。操作如下： 右键AdventureWorks——【任务】——【还原】——【事务日志】，按图13作如下配置： 图13 我们是在16:09分删除数据的，将时间点提前一分钟到16:08分。这里不用太在意，只要把时间点指定到你误删除的时间之前即可。如果不记得具体时间，可以多次尝试几次。 由于日志尾部备份都是最后一个备份文件，所以这里菜单栏的【选项】部分请按如图14做配置： 图14 待一切检查就绪后，点击确定即可开始回滚未提交的事务日志了。

检查是否恢复成功 输入查询语句，查询TEST中的数据。

      USE  AdventureWorks
GO 
SELECT * FROM TEST
      

结果如图15
图15 可以看到正是我们刚才删除前的数据。至此，整个被删除的数据即完全恢复了。


方法局限性 此方法有一定的局限性 首先，就是要求数据的恢复模式是【完整】的，如果之前一直是【简单】模式，此方法不适用 其次，需要有一次完整的备份文件，如果你是管理数据库的，请做好每周一次完整备份的策略。 再次，如果你发现误操作以后还有很多人做了操作，那么你还原成功后，别人的操作也会还原，所以发生误操作后，要马上停止别人对数据库的操作。 最后，这个方法要对数据库独占，所以你想偷偷恢复是不行的了，勇敢的去承认错误吧。

觉得文章内容不错

记得点击右下角「在看」和转发

最后给大家分享我写的SQL两件套： 《SQL基础知识第二版》 和 《SQL高级知识第二版》 的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。

有需要的读者可以下载学习，在下面的公众号「 数据前线 」( 非本号 )后台回复关键字： SQL ，就行

数据前线

后台回复关键字： 1024 ，获取一份精心整理的技术干货

后台回复关键字：进群，带你进入高手如云的交流群

              
                  
                    推荐阅读
                  
                
              

• MySQL 查找并删除重复行
  
• SQL中的递归查询
  
• SQL中的流程控制
  
• SQL中MERGE的用法
  
• 21个写SQL的好习惯