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

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

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

标签：打开   cat   脚本   最大   复数   bst   rop   into   ===