SQLSERVER回滚恢复误操作的数据

2023年12月6日发(作者：)

SQLSERVER回滚恢复误操作的数据

在生产数据库做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 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 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 WHERE NAME = 'BACKUP_'+@TABLENAME AND TYPE = 'U')

BEGIN

DECLARE test_Cursor CURSOR FOR

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_s

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

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 s 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,InsertAS

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_s

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

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

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

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

FROM INFORMATION_s

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 ENDEND ' EXEC (@SQLTR) ENDEND

--------------------- 作者：david-sui

来源：CSDN

原文：/suixufeng/article/details/76653074

版权声明：本文为博主原创文章，转载请附上博文链接！