SQLSERVER回滚恢复误操作的数据
在⽣产数据库做CURD操作时，可能会有执⾏某条语句误操作的情况发⽣，针对这个种情况有两点建议：
、在SQL SERVER上开启事务确认功能，当执⾏完语句后确认⽆误，再提交事务。（开启⽅法见附件图⽚）。
、新建存储过程，粘贴附件脚本。此存储过程执⾏后能够⾃动产⽣两个操作⽇志表，⾃动记录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 lumns
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            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        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+'
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 lumns
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        @INTODECLARE+=''@''+COLUMN_NAME+'','',
@COLUMN2+=''[''+COLUMN_NAME+''],'' ,
@WHERE += ''ISNULL(''+ COLUMN_NAME+'','''''''')=ISNULL(@''+COLUMN_NAME+'','''''''') AND ''
FROM lumns
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    ''
--*********************还原操作之后把备份表和log表的记录删掉*************
DECLARE @SQL_DELGUID VARCHAR(MAX)=''
DELETE FROM BACKUP_'+@TABLENAME+' WHERE  UPDATEGUID IN(SELECT UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AN    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)sqlserver备份表语句
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
SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''INSERT'',@SQL_ISLAST+@SQL_DELETE+
@SQL_DELGUID,@EXECSQL        RETURN
END
END
'
EXEC (@SQLTR)
END
END

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。