SQL SERVER回滚恢复误操作数据的实现方法
2022-11-12 09:21:10
内容摘要
这篇文章主要为大家详细介绍了SQL SERVER回滚恢复误操作数据的实现方法,具有一定的参考价值,可以用来参考一下。
感兴趣SQL SERVER回滚恢复误操作数据的实现方法的小伙伴,下面
文章正文
这篇文章主要为大家详细介绍了SQL SERVER回滚恢复误操作数据的实现方法,具有一定的参考价值,可以用来参考一下。
感兴趣SQL SERVER回滚恢复误操作数据的实现方法的小伙伴,下面一起跟随512笔记的小编罗X来看看吧.在生产数据库做CURD操作时,可能会有执行某条语句误操作的情况发生,针对这个种情况有两点建议:1、在SQL SERVER上开启事务确认功能,当执行完语句后确认无误,再提交事务。(开启方法见附件图片)。2、新建存储过程,粘贴附件脚本。此存储过程执行后能够自动产生两个操作日志表,自动记录CRUD的所有操作。适用于提交事务后才发现错误的情况。只需要打开表UPDATE_LOG,粘贴RollbackupSQL里的语句执行即可恢复数据。注意:1)如果表中有自增长的ID,所恢复数据的ID值是最大ID+1。2)由于正常操作也会回写操作日志,注意及时清理日志表,或者在执行完后删掉新建的存储过程、触发器及表。回滚脚本,执行后数据要记录的表名1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | <code class = "language-sql" >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 </code> |
注:关于SQL SERVER回滚恢复误操作数据的实现方法的内容就先介绍到这里,更多相关文章的可以留意
代码注释