SQL Server rollback recovers data for mis-operation

In the production database to do curd operation, there may be a statement to perform a false operation of the situation occurred, in this case there are two suggestions:

< Span style= "outline:0px; font-family: Equal line; Font-size:14px "> 1, &NBSP; in sql SERVER to turn on transaction acknowledgement function, When the statement is executed, the transaction is confirmed and then committed. (See attached image for opening method).

< Span style= "outline:0px; font-family: Equal line; Font-size:14px "> 2, &NBSP; Create a new stored procedure and paste the attachment script. This stored procedure can automatically generate two operations log tables after execution, automatically recording crud all operations. Applies to cases where an error is found after committing a transaction. You only need to open the table update_log , paste rollbackupsql to recover the data.

Note:1) If there is a self-growing IDin the table, the ID value of the recovered data is the maximum id+1.

2) As normal operation will also write back the operation log, pay attention to clean up the log table in a timely manner, or delete the new stored procedures, triggers and tables after execution.

Rollback script, the name of the table to be logged when the data is executed

CREATE PROCEDURE [dbo].    [Sp_update_log] @TABLENAME VARCHAR (asbegin) SET NOCOUNT on; IF not EXISTS (SELECT * from sys.tables WHERE NAME = @TABLENAME and TYPE = ' U ') BEGIN PRINT ' error:not exist tabl        E ' [email protected] RETURN END IF (@TABLENAME like ' backup_% ' OR @TABLENAME = ' update_log ') BEGIN --print ' error:not exist table ' [email protected] RETURN END--================================ determine existence Update_log table ============================ IF not EXISTS (SELECT * from sys.tables WHERE NAME = ' Update_log ' and TYPE = ' U ') CREATE TABLE update_log (updateguid VARCHAR, UpdateTime DATETIME, T Ablename varchar, Updatetype varchar (6), Rollbacksql varchar (MAX), Execsql varchar (50  0))--================================= determine if there is a backup_ table ================================ if not EXISTS (SELECT * from sys.tables WHERE NAME = ' Backup_ ' [email p rotected] and TYPE = ' U ') BEGIN DECLARE test_cursor Cursor for SELECT column_name,data_type,character_m Aximum_length from Information_schema.columns WHERE [email protected] OPEN test_cursor DECLARE @        SQLTB NVARCHAR (MAX) = "DECLARE @COLUMN_NAME NVARCHAR (), @DATA_TYPE VARCHAR, @CHARACTER_MAXIMUM_LENGTH INT        FETCH NEXT from Test_cursor to @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH while @ @FETCH_STATUS =0 BEGIN SET @[email protected]+ ' [' [email protected]_name+ '] ' [Email protected]_type+case ISNULL (@C  haracter_maximum_length,0) when 0 then "WHEN-1 then" (MAX) ' ELSE ' (' +cast (@CHARACTER_MAXIMUM_LENGTH as VARCHAR (10) + ') ' End+ ', ' FETCH NEXT from Test_cursor to @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH END S ET @SQLTB = ' CREATE TABLE backup_ ' [email protected]+ ' (updateguid varchar, updatetype varchar), ' +substring (    @SQLTB, 1,len (@SQLTB)-1) + ') '    EXEC (@SQLTB) CLOSE test_cursor deallocate test_cursor END--===================================== = Determine if there is an UPDATE trigger ========================= if not EXISTS (SELECT * from sys.objects WHERE NAME = ' Tg_ ' [email prote cted]+ ' _update ' and TYPE = ' TR ') BEGIN DECLARE @SQLTR NVARCHAR (MAX) SET @SQLTR = ' CREATE TRIGGER tg_ ' [email     protected]+ ' _update on ' [email protected]+ ' after Update,delete,insertasbegin SET NOCOUNT on; --============================== get guid========================================== DECLARE @NEWID VARCHAR (=NEWID) ()--=========================== Insert the deleted or new data into the backup table ========================= DECLARE @ROWCOUNT INT INSERT INTO [dbo].    [Backup_ ' [email protected]+ '] SELECT @NEWID, ' DELETE ', * from deleted SET @[email protected] @ROWCOUNT IF @ROWCOUNT >0 BEGIN inser T into [dbo].        [Backup_ ' [email protected]+ '] SELECT @NEWID, ' INSERT ', * from inserted END ELSE BEGIn INSERT into [dbo].        [Backup_ ' [email protected]+ '] SELECT @NEWID, ' INSERT ', * from inserted SET @[email protected] @ROWCOUNT END--======================== ====== log and rollback operations sql===========================--****************** generate the column name used for the INSERT statement (avoid the self-increment field) *****************    DECLARE @COLUMN1 NVARCHAR (MAX) = "' SELECT @COLUMN1 + = ', [' +column_name+ '] ' from information_schema.columns WHERE table_name= ' [email protected]+ ' and ColumnProperty (object_id ("" [email protected]+ '), COLUMN                                               _name, ' isidentity ') <>1--Non-self-increment field SET @COLUMN1 =substring (@COLUMN1, 2,len (@COLUMN1))  --******************* dynamically define variables, delete conditions that match columns ******************** 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), "" and "then"' When '-1 "then" (MAX), ' ELSE ' (' +cast (character_octet_length as VARCHAR) + '), ' END, @INTODECLARE + = ' ' @ ' +column_name+ ', ', @COLUMN2 + = ' [' +column_name+ '], ', @WHERE + = ' ISNULL (' + column_name+ ', ' ") =isnull (@ ' +column_name+ ', ' "') and ' from Information_schema.columns WHERE table_name= '" [email protecte d]+ ' Set @DECLARE =left (@DECLARE, Len (@DECLARE)-1) Set @INTODECLARE =left (@INTODECLARE, Len (@INTODECLARE)-1) Set @c Olumn2=left (@COLUMN2, Len (@COLUMN2)-1) SET @WHERE = left (@WHERE, Len (@WHERE)-3)--******************* determines whether the most recent operation to restore the current table ******************* DECLARE @SQL_ISLAST VARCHAR (MAX) = ' SET NOCOUNT on DECLARE @maxdate datetime SEL ECT @maxdate =max (updatetime) from Update_log WHERE tablename= "" "[email protected]+ '" "IF not EXISTS (SELECT 1 F ROM update_log WHERE [email protected] and updateguid= "" [email protected]+ "] BEGIN DECLARE @M      Axguid VARCHAR (50)  Select @MAXGUID =updateguid from Update_log WHERE [email protected] PRINT "" This action is not the last action, please gradually restore the GUID of this table's most recent operation is: ' [email protected] RETURN END '--******************** sql***************** used to restore insert and update operations * * DECLARE @SQL_DELETE VARCHAR (MAX) = ' Set ROWCOUNT 1--only delete 1 rows under the same conditions DECLARE cursor_ Cursor for Sele CT ' [email protected]+ ' from Backup_ ' [email protected]+ ' WHERE updateguid= ' ' [email protected]+ "" and Updatetype= "" INSERT "" OPEN cursor_ DECLARE "[email protected]+ ' FETCH NEXT from Cursor_ INT O ' [email protected]+ ' while @ @FETCH_STATUS =0 BEGIN DELETE from ' [Email protect ed]+ ' WHERE ' [email protected]+ ' FETCH NEXT from Cursor_ to ' [email protected]+ ' END CLOSE Cu     Rsor_ deallocate cursor_ SET ROWCOUNT 0 "--********************* restore Delete and update operation sql******************* DECLARE @SQL_INSERT VarchaR (MAX) = "INSERT into" [email protected]+ ' SELECT ' [email protected]+ ' from Backup_ ' [email protected ]+ ' WHERE updateguid= ' "" [email protected]+ "" and Updatetype= "" "DELETE" "--********************* restore Delete the backup table and log table records after the operation ************* DECLARE @SQL_DELGUID VARCHAR (MAX) = "DELETE from Backup_" [email protected]+ ' W Here Updateguid in (SELECT updateguid from Update_log WHERE updatetime>[email protected] and tablename= "" [ email protected]+ "") DELETE from Update_log WHERE updatetime>[email protected] and tablename= "" [ email protected]+ ' PRINT ' rollback operation succeeded, total recovery ' +cast (@ROWCOUNT as VARCHAR (10)) + ' record ' ' SET NOCOUNT OFF ' '--********************* perform a restore operation for sql********************************** DECLARE @EXECSQL VARCHAR = ' DECLARE @SQL VARCHAR (MAX) SELECT @SQL =rollbacksql from Update_log WHERE updateguid= "" "[email protected]+" "EXE C (@SQL) "--=============="================ determines which mode of operation is performed ================================= 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 (), ' [email protected]+ ', ' UPDATE ', @[email protected][email protected][ Email protected]_delguid, @EXECSQL RETURN END IF @DoType = ' DELETE ' BEGIN INSERT into [dbo].        [Update_log] SELECT @NEWID, GETDATE (), ' [email protected]+ ', ' ' DELETE ', @[email protected][email protected]_ Delguid, @EXECSQL RETURN END IF @DoType = ' Insert ' BEGIN INSERT INTO [dbo].        [Update_log] SELECT @NEWID, GETDATE (), "[email protected]+", "INSERT", @[email protected][email proTected]_delguid, @EXECSQL RETURN endend ' EXEC (@SQLTR) endend 

SQL Server rollback recovers data for mis-operation