SQL SERVER回滚恢复误操作的数据

最新推荐文章于 2024-04-26 19:52:36 发布
最新推荐文章于 2024-04-26 19:52:36 发布
阅读量1k 收藏
点赞数
文章标签： 数据库 java 开发工具
原文链接：http://www.cnblogs.com/lsgsanxiao/p/10947314.html
版权
在生产数据库做CURD操作时，可能会有执行某条语句误操作的情况发生，针对这个种情况有两点建议：
1、
在SQL SERVER上开启事务确认功能，当执行完语句后确认无误，再提交事务。（开启方法见附件图片）。
2、
新建存储过程，粘贴附件脚本。此存储过程执行后能够自动产生两个操作日志表，自动记录CRUD的所有操作。适用于提交事务后才发现错误的情况。只需要打开表UPDATE_LOG，粘贴RollbackupSQL里的语句执行即可恢复数据。
注意：1)如果表中有自增长的ID，所恢复数据的ID值是最大ID+1。
2)由于正常操作也会回写操作日志，注意及时清理日志表，或者在执行完后删掉新建的存储过程、触发器及表。
回滚脚本，执行后数据要记录的表名 
CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]

    @TABLENAME VARCHAR(50)

AS

BEGIN

    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = 'U' )

    BEGIN

        PRINT'ERROR：not exist table '+@TABLENAME

        RETURN

    END

    IF (@TABLENAME LIKE'BACKUP_%' OR @TABLENAME='UPDATE_LOG' )

    BEGIN

        --PRINT'ERROR：not exist table '+@TABLENAME

        RETURN

    END

    --================================判断是否存在 UPDATE_LOG 表============================

    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'UPDATE_LOG' AND TYPE = 'U')

        CREATE TABLE UPDATE_LOG

        (

            UpdateGUID VARCHAR(36),

            UpdateTime DATETIME,

            TableName varchar(20),

            UpdateType varchar(6),

            RollBackSQL varchar(MAX),

            ExecSQL VARCHAR(500)

        )

    --=================================判断是否存在 BACKUP_ 表================================

    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'BACKUP_'+@TABLENAME AND TYPE = 'U')

    BEGIN

        DECLARE test_Cursor CURSOR FOR

        SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns

        WHERE TABLE_NAME=@TABLENAME

        OPEN test_Cursor

        DECLARE @SQLTB NVARCHAR(MAX)=''

        DECLARE @COLUMN_NAME NVARCHAR(50),@DATA_TYPE VARCHAR(20),@CHARACTER_MAXIMUM_LENGTH INT

        FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH

        WHILE @@FETCH_STATUS=0

        BEGIN

            SET @SQLTB=@SQLTB+'['+@COLUMN_NAME+'] '+@DATA_TYPE+CASE ISNULL(@CHARACTER_MAXIMUM_LENGTH,0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE'('+CAST(@CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+')' END+','

            FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH

        END

        SET @SQLTB='CREATE TABLE BACKUP_'+@TABLENAME+' (UpdateGUID varchar(36),UpdateType Varchar(10),'+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+')'

        EXEC (@SQLTB)

        CLOSE test_Cursor

        DEALLOCATE test_Cursor

    END

    --======================================判断是否存在 UPDATE 触发器=========================

    IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = 'tg_'+@TABLENAME+'_Update' AND TYPE = 'TR')

    BEGIN

        DECLARE @SQLTR NVARCHAR(MAX)

        SET @SQLTR='

CREATE TRIGGER tg_'+@TABLENAME+'_Update

    ON  '+@TABLENAME+'

    AFTER Update,Delete,Insert

AS

BEGIN 

    SET NOCOUNT ON;

    --==============================获取GUID==========================================

    DECLARE @NEWID VARCHAR(36)=NEWID()

 

    --===========================将删掉或新增的数据插入备份表=========================

    DECLARE @ROWCOUNT INT

    INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']

    SELECT @NEWID,''DELETE'',* FROM deleted

    SET @ROWCOUNT=@@ROWCOUNT

    IF @ROWCOUNT>0

    BEGIN

        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']

        SELECT @NEWID,''INSERT'',* FROM inserted

    END

    ELSE

    BEGIN

        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']

        SELECT @NEWID,''INSERT'',* FROM inserted

        SET @ROWCOUNT=@@ROWCOUNT

    END

 

    --==============================记录日志和回滚操作的SQL===========================

              

 

    --******************生成插入语句用到的列名（需避开自增字段）********************

    DECLARE @COLUMN1 NVARCHAR(MAX)=''''

    SELECT @COLUMN1+='',[''+COLUMN_NAME+'']'' FROM INFORMATION_SCHEMA.columns

    WHERE TABLE_NAME='''+@TABLENAME+'''

    AND COLUMNPROPERTY(OBJECT_ID('''+@TABLENAME+'''),COLUMN_NAME,''IsIdentity'')<>1 --非自增字段

    SET @COLUMN1=SUBSTRING(@COLUMN1,2,LEN(@COLUMN1))

 

               

                           

    --*******************动态定义变量、删除条件匹配的列********************

    DECLARE @DECLARE VARCHAR(MAX)='''',@INTODECLARE VARCHAR(MAX)='''',@WHERE VARCHAR(MAX)='''',@COLUMN2 VARCHAR(MAX)=''''

    SELECT @DECLARE+=''@''+COLUMN_NAME+'' ''+DATA_TYPE+CASE ISNULL(CAST(CHARACTER_OCTET_LENGTH AS VARCHAR(10)),'''') WHEN '''' THEN '','' WHEN ''-1'' THEN ''(MAX),'' ELSE ''(''+CAST(CHARACTER_OCTET_LENGTH AS VARCHAR(10))+''),'' END,

        @INTODECLARE+=''@''+COLUMN_NAME+'','',

        @COLUMN2+=''[''+COLUMN_NAME+''],'' ,

        @WHERE += ''ISNULL(''+ COLUMN_NAME+'','''''''')=ISNULL(@''+COLUMN_NAME+'','''''''') AND ''

    FROM INFORMATION_SCHEMA.columns

    WHERE TABLE_NAME='''+@TABLENAME+'''

    SET @DECLARE=LEFT(@DECLARE,LEN(@DECLARE)-1)

    SET @INTODECLARE=LEFT(@INTODECLARE,LEN(@INTODECLARE)-1)

    SET @COLUMN2=LEFT(@COLUMN2,LEN(@COLUMN2)-1)

    SET @WHERE= LEFT(@WHERE,LEN(@WHERE)-3)

   

    --*******************判断是否还原当前表的最近一次操作*******************         

    DECLARE @SQL_ISLAST VARCHAR(MAX)=''

    SET NOCOUNT ON

    DECLARE @maxdate datetime

    SELECT @maxdate=max(updatetime) FROM UPDATE_LOG WHERE TableName='''''+@TABLENAME+'''''

    IF NOT EXISTS(SELECT 1 FROM UPDATE_LOG WHERE UpdateTime=@maxdate AND UPDATEGUID=''''''+@NEWID+'''''')

    BEGIN

        DECLARE @MAXGUID VARCHAR(50)

        SELECT @MAXGUID=UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime=@maxdate

        PRINT ''''此操作并非最近一次操作，请逐步还原,此表最近一次操作的GUID是：''''+@MAXGUID

        RETURN

    END

    ''

 

    --********************还原insert和update操作用到的SQL*******************

 

    DECLARE @SQL_DELETE VARCHAR(MAX)=''

    SET ROWCOUNT 1  --设定相同条件下只删除1行        

    DECLARE Cursor_ CURSOR FOR

    SELECT ''+@COLUMN2+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID= ''''''+@NEWID+'''''' AND UpdateType=''''INSERT''''

    OPEN Cursor_

    DECLARE ''+@DECLARE+''

    FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''

    WHILE @@FETCH_STATUS=0

    BEGIN                  

        DELETE FROM '+@TABLENAME+' WHERE ''+@WHERE+''

        FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''

    END

    CLOSE Cursor_

    DEALLOCATE Cursor_

    SET ROWCOUNT 0

    ''

 

    --*********************还原delete和update操作用到的SQL*******************

 

    DECLARE @SQL_INSERT VARCHAR(MAX)=''

    INSERT INTO '+@TABLENAME+' SELECT ''+@COLUMN1+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID=''''''+@NEWID+'''''' AND UpdateType=''''DELETE''''

    ''

 

    --*********************还原操作之后把备份表和log表的记录删掉*************

 

    DECLARE @SQL_DELGUID VARCHAR(MAX)=''

    DELETE FROM BACKUP_'+@TABLENAME+' WHERE  UPDATEGUID IN(SELECT UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+''''')

    DELETE FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+'''''

    PRINT ''''回滚操作执行成功，共恢复 ''+CAST(@ROWCOUNT AS VARCHAR(10))+'' 条记录''''

    SET NOCOUNT OFF

    ''

 

    --*********************执行还原操作的SQL**********************************

 

    DECLARE @EXECSQL VARCHAR(500)=''

    DECLARE @SQL VARCHAR(MAX)

    SELECT @SQL=ROLLBACKSQL FROM UPDATE_LOG WHERE UPDATEGUID=''''''+@NEWID+''''''  

    EXEC(@SQL) 

    ''

 

    --==============================判断执行的哪种操作方式=================================

 

    DECLARE @DoType VARCHAR(MAX)=''UPDATE''

    IF NOT EXISTS(SELECT 1 FROM deleted)

        SET @DoType=''INSERT''

    IF NOT EXISTS(SELECT 1 FROM inserted)

        SET @DoType=''DELETE''

    IF NOT EXISTS(SELECT 1 FROM deleted) AND  NOT EXISTS(SELECT 1 FROM inserted)

        RETURN

    IF @DoType=''UPDATE''

    BEGIN

        INSERT INTO [dbo].[UPDATE_LOG]

        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''UPDATE'',@SQL_ISLAST+@SQL_DELETE+@SQL_INSERT+@SQL_DELGUID,@EXECSQL

        RETURN

    END

    IF @DoType=''DELETE''

    BEGIN

        INSERT INTO [dbo].[UPDATE_LOG]

        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''DELETE'',@SQL_ISLAST+@SQL_INSERT+@SQL_DELGUID,@EXECSQL

        RETURN

    END

    IF @DoType=''INSERT''

    BEGIN

        INSERT INTO [dbo].[UPDATE_LOG]

        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''INSERT'',@SQL_ISLAST+@SQL_DELETE+@SQL_DELGUID,@EXECSQL

        RETURN

    END

END

            '

        EXEC (@SQLTR)

    END

END

 


---------------------
作者：david-sui
来源：CSDN
原文：https://blog.csdn.net/suixufeng/article/details/76653074
版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/lsgsanxiao/p/10947314.html

weixin_30454481
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
误删除数据，怎么恢复或回滚RDS数据库？
01-06
数据恢复的方式跟备份的方式有关。比如物理备份还是逻辑备份，是否开启了日志备份。所谓日志备份其实就是日志，如果开启了日志备份，那可以恢复到备份范围内的的任意时间点。 下面按数据库类型分别列举数据恢复的方法。 mysql 恢复某个备份或者恢复到某时间点 恢复单个库或表，或者恢复多个库或表 恢复到其它地域，这个主要是用于快速在另一个地域建立数据库 sql server 恢复mssql备份或者恢复到时间点 恢复到一个临时数据库，可作为临时验证使用 登录临时数据库 pg 恢复备份或者恢复到时间点 把本地备份恢复到云上 ppas或oracle 恢复备份或者恢复到时间点 mariadb 恢复mariadb备
SQL Server 2014 数据库误删数据的恢复处理
LZD_jay的专栏
02-03 3542
ApexSQL Log 是一款优秀的数据库恢复软件，基本支覆盖 SQL Server 主流版本（最新发布说明点击查看，根据当前安装的SQL Server版本来选择对应支持的 ApexSQL Log 版本），能恢复Delete/Update 等误操作的数据并生成相应的脚本。
SQLServer 2008以上误操作数据库恢复方法——日志尾部备份
数据库
01-10 1099
问题： 经常看到有人误删数据，或者误操作，特别是update和delete的时候没有加where，然后就喊爹喊娘了。人非圣贤孰能无过，做错可以理解，但不能纵容，这个以后再说，现在先来解决问题。 遇到这种情况，一般都是没有做备份，不然也不会来发问了。首先要冷静，否则会有更大的灾难。直到你放弃。 解决方法： 对于这类问题，主要是找回误操作之前的数据，在2008...
Sql Server 回滚
qq_40686925的博客
04-01 1829
当我们在操作数据库时，有时会同时对多个表进行增删改，且这些表具有上下级的关联性，如果上级表数据操作失败，下级表数据操作成功，这时数据库中就会产生无用的数据。 sql语句中具有回滚事务，当启用事务后，sql语句出现错误时，就会将操作的数据还原。 BEGIN TRANSACTION--开始事务 DECLARE @errorSun INT --定义错误计数器 SET @errorSun=0 --没错为0 UPDATE aa SET id=2 WHERE a=1 --事务操作SQL语句,可以是存储过程 SE
SQL中如何用快照，恢复被误删的数据？
xuexihao1234的博客
07-09 1116
在创建数据库快照时，源数据库通常会有打开的事务。在快照可以使用之前，打开的事务会回滚以使数据库快照在事务上取得一致。现在的快照应该和我们新建的数据库一摸一样，可以通过sql server的对象浏览器查看数据库快照，我们可以通过USE [快照库名]来查询数据库快照。3、为防止误删除，必须在数据插入或更新到数据库之后再创建快照，否则在插入或更新之前创建快照，插入或更新的数据被删除是无法恢复的。上面删除是我们模拟误删了数据库中的数据，但是幸好我们做了数据库的快照。数据库快照下面没有任何快照了，说明已经被删除。
sqlsever回滚操作_SQL SERVER回滚恢复误操作的数据
weixin_30910893的博客
12-24 1308
CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]@TABLENAME VARCHAR(50)ASBEGINSET NOCOUNT ON;IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = 'U')BEGINPRINT'ERROR：not exist table'+@TABLE...
SQLServer数据库误操作如何恢复
热门推荐
鲤籽鲲的博客
11-23 1万+
SQLServer 误操作恢复数据
恢复SQL Server被误删除的数据（再扩展）
dingshen2767的博客
07-04 414
恢复SQL Server被误删除的数据（再扩展） 大家对本人之前的文章《恢复SQL Server被误删除的数据》 反应非常热烈，但是文章里的存储过程不能实现对备份出来的日志备份里所删数据的恢复 这个是一个缺陷，本人决定对这个存储过程扩展一下，支持对log backup文件里的delete语句进行恢复 实验步骤 1、首先先准备好测试表和测试语句 USE [s...
MySQL数据库误Update找回数据的方法
乌龟的专栏
11-17 5363
MySQL数据库误Update找回数据的方法
盘点一下数据库的误操作有哪些后悔药？
weixin_50205273的博客
03-19 380
前言 无论是开发、测试，还是DBA，都难免会涉及到数据库的操作，比如：创建某张表，添加某个字段、添加数据、更新数据、删除数据、查询数据等等。 正常情况下还好，但如果操作数据库时出现失误，比如： 删除订单数据时where条件写错了，导致多删了很多用户订单。 更新会员有效时间时，一次性把所有会员的有效时间都更新了。 修复线上数据时，改错了，想还原。 还有很多很多场景，我就不一一列举了。 如果出现线上环境数据库误操作怎么办?有没有后悔药? 答案是有的，请各位看官仔细往下看。 1.不要用聊天工具
sql server误操作日志还原工具
07-03
sql server误操作日志还原工具，用于误操作后回滚。使用教程地址：https://www.cnblogs.com/haocool/p/3147447.html
MySQL误操作后如何快速恢复数据
01-19
误操作后，能快速回滚数据是非常重要的。 　传统解法 　用全量备份重搭实例，再利用增量binlog备份，恢复到误操作之前的状态。然后跳过误操作的SQL，再继续应用binlog。此法费时费力，不值得再推荐。 　利用...
SQLserver日志恢复工具 v1.0.zip
07-14
SQLserver日志恢复工具适用版本SQL2005或SQL2008版本,用于查看日志和恢复误删除数据，针对误删除MSSQL数据行的恢复。主要针对deletefrom 语句。 SQLserver日志恢复工具具体使用简介： 程序功能：找回误删除的数据...
MySQL误操作后快速恢复数据的方法
12-16
误操作后，能快速回滚数据是非常重要的。 传统解法 用全量备份重搭实例，再利用增量binlog备份，恢复到误操作之前的状态。然后跳过误操作的SQL，再继续应用binlog。此法费时费力，不值得再推荐。 利用binlog2sql快速...
MySQL数据库误操作后快速回滚的方法
12-15
误操作后，能快速回滚数据是非常重要的。 binlog2sql快速回滚 首先，确认你的MySQL server开启了binlog，设置了以下参数: [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_size = 1000M...
【kettle001】访问国产达梦数据库并处理数据至execl文件
kngines
04-22 432
一直以来想写下基于kettle的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。熟悉、梳理、总结下达梦（DM）关系型数据库相关知识体系。
探秘MySQL主从复制的多种实现方式
最新发布
todoitbo的博客
04-26 659
本文将深入探讨MySQL主从复制的多种实现方式，包括基于语句、基于行和混合模式等。无论您是初学者还是有经验的数据库管理员，都能通过本文全面了解MySQL主从复制技术的多样化选择，从而提高数据库的可用性和性能。
redis分布式锁到底怎么用
LinggoLing的博客
04-22 446
分布式锁到底怎么用
mysql-connector-java和spring-boot-starter-jdbc和mybatis-spring-boot-start
xzy的博客
04-23 960
JDBC是使用java语言操作mysql数据库的规范，java语言必须按照这个规范写才可以操作mysql数据库。
sqlserver误删除数据回滚
09-28
在SQL Server中，如果发生了误操作导致数据被误删除，可以通过事务日志来进行回滚恢复。首先，确保数据库的恢复模式为完整，这是数据恢复的前提条件之一。然后，可以按照以下步骤进行回滚操作： 1. 使用`SELECT * ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值