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回滚恢复误操作数据的实现方法的内容就先介绍到这里,更多相关文章的可以留意

代码注释

作者:喵哥笔记

IDC笔记

学的不仅是技术,更是梦想!