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

在生产数据库做CURD操作时，可能会有执行某条语句误操作的情况发生，针对这个种情况有两点建议：
1、
在SQL SERVER上开启事务确认功能，当执行完语句后确认无误，再提交事务。（开启方法见附件图片）。
2、
新建存储过程，粘贴附件脚本。此存储过程执行后能够自动产生两个操作日志表，自动记录CRUD的所有操作。适用于提交事务后才发现错误的情况。只需要打开表UPDATE_LOG，粘贴RollbackupSQL里的语句执行即可恢复数据。
注意：1)如果表中有自增长的ID，所恢复数据的ID值是最大ID+1。
2)由于正常操作也会回写操作日志，注意及时清理日志表，或者在执行完后删掉新建的存储过程、触发器及表。

回滚脚本，执行后数据要记录的表名

1CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]
2
3    @TABLENAME VARCHAR(50)
4
5AS
6
7BEGIN
8
9    SET NOCOUNT ON;
10
11    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = 'U' )
12
13    BEGIN
14
15        PRINT'ERROR：not exist table '+@TABLENAME
16
17        RETURN
18
19    END
20
21    IF (@TABLENAME LIKE'BACKUP_%' OR @TABLENAME='UPDATE_LOG' )
22
23    BEGIN
24
25        --PRINT'ERROR：not exist table '+@TABLENAME
26
27        RETURN
28
29    END
30
31    --================================判断是否存在 UPDATE_LOG 表============================
32
33    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'UPDATE_LOG' AND TYPE = 'U')
34
35        CREATE TABLE UPDATE_LOG
36
37        (
38
39            UpdateGUID VARCHAR(36),
40
41            UpdateTime DATETIME,
42
43            TableName varchar(20),
44
45            UpdateType varchar(6),
46
47            RollBackSQL varchar(MAX),
48
49            ExecSQL VARCHAR(500)
50
51        )
52
53    --=================================判断是否存在 BACKUP_ 表================================
54
55    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'BACKUP_'+@TABLENAME AND TYPE = 'U')
56
57    BEGIN
58
59        DECLARE test_Cursor CURSOR FOR
60
61        SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns
62
63        WHERE TABLE_NAME=@TABLENAME
64
65        OPEN test_Cursor
66
67        DECLARE @SQLTB NVARCHAR(MAX)=''
68
69        DECLARE @COLUMN_NAME NVARCHAR(50),@DATA_TYPE VARCHAR(20),@CHARACTER_MAXIMUM_LENGTH INT
70
71        FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH
72
73        WHILE @@FETCH_STATUS=0
74
75        BEGIN
76
77            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+','
78
79            FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH
80
81        END
82
83        SET @SQLTB='CREATE TABLE BACKUP_'+@TABLENAME+' (UpdateGUID varchar(36),UpdateType Varchar(10),'+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+')'
84
85        EXEC (@SQLTB)
86
87        CLOSE test_Cursor
88
89        DEALLOCATE test_Cursor
90
91    END
92
93    --======================================判断是否存在 UPDATE 触发器=========================
94
95    IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = 'tg_'+@TABLENAME+'_Update' AND TYPE = 'TR')
96
97    BEGIN
98
99        DECLARE @SQLTR NVARCHAR(MAX)
100
101        SET @SQLTR='
102
103CREATE TRIGGER tg_'+@TABLENAME+'_Update
104
105    ON  '+@TABLENAME+'
106
107    AFTER Update,Delete,Insert
108
109AS
110
111BEGIN 
112
113    SET NOCOUNT ON;
114
115    --==============================获取GUID==========================================
116
117    DECLARE @NEWID VARCHAR(36)=NEWID()
118
119 
120
121    --===========================将删掉或新增的数据插入备份表=========================
122
123    DECLARE @ROWCOUNT INT
124
125    INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']
126
127    SELECT @NEWID,''DELETE'',* FROM deleted
128
129    SET @ROWCOUNT=@@ROWCOUNT
130
131    IF @ROWCOUNT>0
132
133    BEGIN
134
135        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']
136
137        SELECT @NEWID,''INSERT'',* FROM inserted
138
139    END
140
141    ELSE
142
143    BEGIN
144
145        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']
146
147        SELECT @NEWID,''INSERT'',* FROM inserted
148
149        SET @ROWCOUNT=@@ROWCOUNT
150
151    END
152
153 
154
155    --==============================记录日志和回滚操作的SQL===========================
156
157              
158
159 
160
161    --******************生成插入语句用到的列名（需避开自增字段）********************
162
163    DECLARE @COLUMN1 NVARCHAR(MAX)=''''
164
165    SELECT @COLUMN1+='',[''+COLUMN_NAME+'']'' FROM INFORMATION_SCHEMA.columns
166
167    WHERE TABLE_NAME='''+@TABLENAME+'''
168
169    AND COLUMNPROPERTY(OBJECT_ID('''+@TABLENAME+'''),COLUMN_NAME,''IsIdentity'')<>1 --非自增字段
170
171    SET @COLUMN1=SUBSTRING(@COLUMN1,2,LEN(@COLUMN1))
172
173 
174
175               
176
177                           
178
179    --*******************动态定义变量、删除条件匹配的列********************
180
181    DECLARE @DECLARE VARCHAR(MAX)='''',@INTODECLARE VARCHAR(MAX)='''',@WHERE VARCHAR(MAX)='''',@COLUMN2 VARCHAR(MAX)=''''
182
183    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,
184
185        @INTODECLARE+=''@''+COLUMN_NAME+'','',
186
187        @COLUMN2+=''[''+COLUMN_NAME+''],'' ,
188
189        @WHERE += ''ISNULL(''+ COLUMN_NAME+'','''''''')=ISNULL(@''+COLUMN_NAME+'','''''''') AND ''
190
191    FROM INFORMATION_SCHEMA.columns
192
193    WHERE TABLE_NAME='''+@TABLENAME+'''
194
195    SET @DECLARE=LEFT(@DECLARE,LEN(@DECLARE)-1)
196
197    SET @INTODECLARE=LEFT(@INTODECLARE,LEN(@INTODECLARE)-1)
198
199    SET @COLUMN2=LEFT(@COLUMN2,LEN(@COLUMN2)-1)
200
201    SET @WHERE= LEFT(@WHERE,LEN(@WHERE)-3)
202
203   
204
205    --*******************判断是否还原当前表的最近一次操作*******************         
206
207    DECLARE @SQL_ISLAST VARCHAR(MAX)=''
208
209    SET NOCOUNT ON
210
211    DECLARE @maxdate datetime
212
213    SELECT @maxdate=max(updatetime) FROM UPDATE_LOG WHERE TableName='''''+@TABLENAME+'''''
214
215    IF NOT EXISTS(SELECT 1 FROM UPDATE_LOG WHERE UpdateTime=@maxdate AND UPDATEGUID=''''''+@NEWID+'''''')
216
217    BEGIN
218
219        DECLARE @MAXGUID VARCHAR(50)
220
221        SELECT @MAXGUID=UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime=@maxdate
222
223        PRINT ''''此操作并非最近一次操作，请逐步还原,此表最近一次操作的GUID是：''''+@MAXGUID
224
225        RETURN
226
227    END
228
229    ''
230
231 
232
233    --********************还原insert和update操作用到的SQL*******************
234
235 
236
237    DECLARE @SQL_DELETE VARCHAR(MAX)=''
238
239    SET ROWCOUNT 1  --设定相同条件下只删除1行        
240
241    DECLARE Cursor_ CURSOR FOR
242
243    SELECT ''+@COLUMN2+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID= ''''''+@NEWID+'''''' AND UpdateType=''''INSERT''''
244
245    OPEN Cursor_
246
247    DECLARE ''+@DECLARE+''
248
249    FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''
250
251    WHILE @@FETCH_STATUS=0
252
253    BEGIN                  
254
255        DELETE FROM '+@TABLENAME+' WHERE ''+@WHERE+''
256
257        FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''
258
259    END
260
261    CLOSE Cursor_
262
263    DEALLOCATE Cursor_
264
265    SET ROWCOUNT 0
266
267    ''
268
269 
270
271    --*********************还原delete和update操作用到的SQL*******************
272
273 
274
275    DECLARE @SQL_INSERT VARCHAR(MAX)=''
276
277    INSERT INTO '+@TABLENAME+' SELECT ''+@COLUMN1+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID=''''''+@NEWID+'''''' AND UpdateType=''''DELETE''''
278
279    ''
280
281 
282
283    --*********************还原操作之后把备份表和log表的记录删掉*************
284
285 
286
287    DECLARE @SQL_DELGUID VARCHAR(MAX)=''
288
289    DELETE FROM BACKUP_'+@TABLENAME+' WHERE  UPDATEGUID IN(SELECT UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+''''')
290
291    DELETE FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+'''''
292
293    PRINT ''''回滚操作执行成功，共恢复 ''+CAST(@ROWCOUNT AS VARCHAR(10))+'' 条记录''''
294
295    SET NOCOUNT OFF
296
297    ''
298
299 
300
301    --*********************执行还原操作的SQL**********************************
302
303 
304
305    DECLARE @EXECSQL VARCHAR(500)=''
306
307    DECLARE @SQL VARCHAR(MAX)
308
309    SELECT @SQL=ROLLBACKSQL FROM UPDATE_LOG WHERE UPDATEGUID=''''''+@NEWID+''''''  
310
311    EXEC(@SQL) 
312
313    ''
314
315 
316
317    --==============================判断执行的哪种操作方式=================================
318
319 
320
321    DECLARE @DoType VARCHAR(MAX)=''UPDATE''
322
323    IF NOT EXISTS(SELECT 1 FROM deleted)
324
325        SET @DoType=''INSERT''
326
327    IF NOT EXISTS(SELECT 1 FROM inserted)
328
329        SET @DoType=''DELETE''
330
331    IF NOT EXISTS(SELECT 1 FROM deleted) AND  NOT EXISTS(SELECT 1 FROM inserted)
332
333        RETURN
334
335    IF @DoType=''UPDATE''
336
337    BEGIN
338
339        INSERT INTO [dbo].[UPDATE_LOG]
340
341        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''UPDATE'',@SQL_ISLAST+@SQL_DELETE+@SQL_INSERT+@SQL_DELGUID,@EXECSQL
342
343        RETURN
344
345    END
346
347    IF @DoType=''DELETE''
348
349    BEGIN
350
351        INSERT INTO [dbo].[UPDATE_LOG]
352
353        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''DELETE'',@SQL_ISLAST+@SQL_INSERT+@SQL_DELGUID,@EXECSQL
354
355        RETURN
356
357    END
358
359    IF @DoType=''INSERT''
360
361    BEGIN
362
363        INSERT INTO [dbo].[UPDATE_LOG]
364
365        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''INSERT'',@SQL_ISLAST+@SQL_DELETE+@SQL_DELGUID,@EXECSQL
366
367        RETURN
368
369    END
370
371END
372
373            '
374
375        EXEC (@SQLTR)
376
377    END
378
379END

---------------------
作者：david-sui
来源：CSDN
原文：https://blog.csdn.net/suixufeng/article/details/76653074
版权声明：本文为博主原创文章，转载请附上博文链接！