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

最新推荐文章于 2024-10-12 11:43:38 发布
最新推荐文章于 2024-10-12 11:43:38 发布
阅读量3k 收藏 7
点赞数
分类专栏： sql server 文章标签： sql
原文链接：https://blog.csdn.net/suixufeng/article/details/76653074
版权

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

在生产数据库做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
版权声明：本文为博主原创文章，转载请附上博文链接！

ice_bird
关注
专栏目录
sqlsever回滚操作_SqlServer事务回滚(2)
weixin_39849239的博客
12-24 2023
SQL事务一、事务概念事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。这特别适用于多用户同时操作的数据通信系统。例如：订票、银行、保险公司以及证券交易系统等。二、事务属性事务4大属性：1 原子性(Atomicity):事务是一个完整的操作。2...
SQL Server2008 数据库误删除数据的恢复方法分享
12-15
3. 在恢复配置界面选择Custom，以便能从日志中恢复数据。 4. 在恢复选项中，勾选Search for deleted records，并指定日志文件路径。 5. 设置目标文件夹以保存恢复过程生成的SQL和bat文件。 6. 开始恢复操作，软件会...
SQLServer恢复表级数据详解
09-10
主要介绍了SQLServer中用于快速恢复表，而不是库，但是切记，防范总比亡羊补牢好,需要的朋友可以参考下
SqlServer数据库误操作进行恢复
weixin_62463536的博客
08-24 1544
此文章讲述了如何数据库如何进行恢复操作，比如在重要数据库中执行了删除（DELETE）或者修改（UPDATE）语句后面还没加条件（WHERE），导致数据库中所有的语句都没了或者删了，上万条数据想一条条从新添加都添加不了，所以就可以用到此文章的内容（注意：恢复之前数据库的恢复模式就要是完整，在此之前必须有一次数据库的完整备份，要保证数据库的日志不丢失）
oracle操作回退SQL
最新发布
琅枫的博客
10-12 495
注意：使用 truncate table 命令删除的表数据，无法通过flashback的方式找回数据。对表数据进行操作后，提交了事务，发现数据更新或者删除错了，这时候还是有方法可以数据回退的。如果闪回恢复数据提示：因为未启用行移动功能,不能闪回表。执行以下语句，允许行移动。再次执行闪回恢复数据的SQL。数据已经回退到update之前的内容了。
tf.control_dependencies(control_inputs)
weixin_36670529的博客
04-08 335
此函数指定某些操作执行的依赖关系 返回一个控制依赖的上下文管理器，使用 with 关键字可以让在这个上下文环境中的操作都在 control_inputs 执行 with tf.control_dependencies([a, b]): c = .... d = ... 在执行完 a，b 操作之后，才能执行 c，d 操作。意思就是 c，d 操作依赖 a，b 操作 with tf.control_dependencies([train_step, variable_averages
记重大失误（SQLserver数据回滚）ApexSQL
weixin_30530939的博客
03-13 260
敲了一天代码，脑壳昏,更改数据时忘记加where条件,该数据库又未备份.修改了1800条数据。。当时那个着急啊。 各种找解决方法，最后使用ApexSQL Log 完美解决。赞一个ApexSQL Log。然后记住数据库开启自动备份。。 转载于:https://www.cnblogs.com/toloe/p/8560380.html...
事务执行sql脚本，错误后回滚
m0_53077601的博客
05-13 4038
1.手动commit，rollback -- 开启事务 START TRANSACTION; -- 关闭自动提交 SET autocommit = 0; -- 要执行的sql INSERT INTO `t_bank_organ_copy1` VALUES ( 1, 975, '06285', '中国银行苏州分行', 1, 'root', '2022-05-10 17:55:46', 'root', '2022-05-10 17:55:46' ); INSERT INTO `t_bank_organ_
SqlServer2008误操作数据(delete或者update)后恢复数据的方法
09-10
本篇文章将详细讲解如何利用SQL Server的事务日志功能来恢复误操作的数据。 首先，要强调的是，在进行任何可能影响数据库的操作时，必须确保有最新的完整数据库备份。在SQL Server 2008中，数据库的恢复模式默认是...
sqlserver2012误删除数据恢复3
08-01
本文将详细介绍如何在没有备份的情况下，利用SQL Server 2012的一些特性来尝试恢复数据。 首先，我们需要理解SQL Server的数据存储结构。数据库是由一系列数据文件（MDF）和日志文件（LDF）组成的，这些文件存储了...
sql server误操作日志还原工具
07-03
sql server误操作日志还原工具，用于误操作后回滚。使用教程地址：https://www.cnblogs.com/haocool/p/3147447.html
update误操作，如何数据回滚（MaydayMayday）
热门推荐
白嫖怪的博客
05-13 1万+
update误操作，数据回滚操作
sql server 怎么根据日志回滚
weixin_42306055的博客
08-12 99
我整理的一些关于【SQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkR基于SQL Server的日志回滚方案 在数据库管理中，日志回滚是一个关键操作，用于确保数据的一致性和完整性。对于SQL Server而言，日志回滚可以帮助恢复到某一特定事务之前的状态。本文...
Oracle使用updateSQL语句忘记加条件，误改了其他数据的回滚方法
u010741112的博客
11-16 1210
执行完之后，就会回到这个误操作之前啦，然后在执行正确的语句，便可以完成，对数据库操作一定要仔细仔细再仔细！Oracle使用updateSQL语句忘记加条件，误改了其他数据的回滚方法。误改后不管是保存还是暂存，所有审批状态都变成了审核通过-。上面语句的table和时间要根据自己的情况进行修改。这是其他单据的审批状态。（1）第一条SQL语句。（2）第二条SQL语句。（3）第三条SQL语句。
Sql Server 回滚
qq_40686925的博客
04-01 2024
当我们在操作数据库时，有时会同时对多个表进行增删改，且这些表具有上下级的关联性，如果上级表数据操作失败，下级表数据操作成功，这时数据库中就会产生无用的数据。 sql语句中具有回滚事务，当启用事务后，sql语句出现错误时，就会将操作的数据还原。 BEGIN TRANSACTION--开始事务 DECLARE @errorSun INT --定义错误计数器 SET @errorSun=0 --没错为0 UPDATE aa SET id=2 WHERE a=1 --事务操作SQL语句,可以是存储过程 SE
SqlServer 数据库操作回滚 撤回操作
CoolDog_1的博客
11-02 4842
如果在 SQL Server 数据库中不小心执行了 UPDATE 语句并且没有备份数据库的话..可能基本上就g了。所以避免这种情况我们可以使用下面这个事务更新；2，然后我们执行撤回操作，然后查询看一下。发现已经被撤回操作了。1，首先更新语句,不加条件，可以看到数据已经被修改了；
sqlserver误删除数据回滚
09-28
在SQL Server中，如果发生了误操作导致数据被误删除，可以通过事务日志来进行回滚恢复。首先，确保数据库的恢复模式为完整，这是数据恢复的前提条件之一。然后，可以按照以下步骤进行回滚操作： 1. 使用`SELECT * FROM UserInfo`语句确认误删除操作之前的数据状态。 2. 使用`SELECT * FROM UserInfo2`语句确认误操作新增的表格数据。 3. 使用`ROLLBACK`语句回滚事务，将数据库恢复到误操作之前的状态。 回滚操作会将所有未提交的事务撤销，恢复到事务开始之前的状态，从而实现数据的回滚。请确保在执行回滚操作之前确认当前事务是否已提交。 如果数据库的数据备份是定期进行的，并且存在可用的备份文件，还可以通过还原备份文件来回复数据。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值