SQL SERVER復原恢複誤操作的資料

標籤：apple   proc   最大   sys   RoCE   自動   sql   statistic   nvarchar   

 

在生產資料庫做CURD操作時，可能會有執行某條語句誤操作的情況發生，針對這個種情況有兩點建議：

1、 在SQL SERVER上開啟事務確認功能，當執行完語句後確認無誤，再提交事務。（開啟方法見附件圖片）。

2、 建立預存程序，粘貼附件指令碼。此預存程序執行後能夠自動產生兩個動作記錄表，自動記錄CRUD的所有操作。適用於提交事務後才發現錯誤的情況。只需要開啟表UPDATE_LOG，粘貼RollbackupSQL裡的語句執行即可恢複資料。

注意：1)如果表中有自增長的ID，所恢複資料的ID值是最大ID+1。

2)由於正常操作也會回寫動作記錄，注意及時清理日誌表，或者在執行完後刪掉建立的預存程序、觸發器及表。

 復原指令碼，執行後資料要記錄的表名

CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]    @TABLENAME VARCHAR(50)ASBEGIN    SET NOCOUNT ON;    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = ‘U‘ )    BEGIN        PRINT‘ERROR：not exist table ‘[email protected]        RETURN    END    IF (@TABLENAME LIKE‘BACKUP_%‘ OR @TABLENAME=‘UPDATE_LOG‘ )    BEGIN        --PRINT‘ERROR：not exist table ‘[email protected]        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_‘[email protected] AND TYPE = ‘U‘)    BEGIN        DECLARE test_Cursor CURSOR FOR        SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns        WHERE [email protected]        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 @[email protected]+‘[‘[email protected]_NAME+‘] ‘[email protected]_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_‘[email protected]+‘ (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_‘[email protected]+‘_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;    --==============================擷取GUID==========================================    DECLARE @NEWID VARCHAR(36)=NEWID()     --===========================將刪掉或新增的資料插入備份表=========================    DECLARE @ROWCOUNT INT    INSERT INTO [dbo].[BACKUP_‘[email protected]+‘]    SELECT @NEWID,‘‘DELETE‘‘,* FROM deleted    SET @[email protected]@ROWCOUNT    IF @ROWCOUNT>0    BEGIN        INSERT 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     --==============================記錄日誌和復原操作的SQL===========================                   --******************產生插入語句用到的列名（需避開自增欄位）********************    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 --非自增欄位    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=‘‘‘[email protected]+‘‘‘    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=‘‘‘‘‘[email protected]+‘‘‘‘‘    IF NOT EXISTS(SELECT 1 FROM UPDATE_LOG WHERE [email protected] AND UPDATEGUID=‘‘‘‘‘‘[email protected]+‘‘‘‘‘‘)    BEGIN        DECLARE @MAXGUID VARCHAR(50)        SELECT @MAXGUID=UPDATEGUID FROM UPDATE_LOG WHERE [email protected]        PRINT ‘‘‘‘此操作並非最近一次操作，請逐步還原,此表最近一次操作的GUID是：‘‘‘‘[email protected]        RETURN    END    ‘‘     --********************還原insert和update操作用到的SQL*******************     DECLARE @SQL_DELETE VARCHAR(MAX)=‘‘    SET ROWCOUNT 1  --設定相同條件下只刪除1行            DECLARE Cursor_ CURSOR FOR    SELECT ‘‘[email protected]+‘‘ FROM BACKUP_‘[email protected]+‘ WHERE UPDATEGUID= ‘‘‘‘‘‘[email protected]+‘‘‘‘‘‘ AND UpdateType=‘‘‘‘INSERT‘‘‘‘    OPEN Cursor_    DECLARE ‘‘[email protected]+‘‘    FETCH NEXT FROM Cursor_ INTO ‘‘[email protected]+‘‘    WHILE @@FETCH_STATUS=0    BEGIN                          DELETE FROM ‘[email protected]+‘ WHERE ‘‘[email protected]+‘‘        FETCH NEXT FROM Cursor_ INTO ‘‘[email protected]+‘‘    END    CLOSE Cursor_    DEALLOCATE Cursor_    SET ROWCOUNT 0    ‘‘     --*********************還原delete和update操作用到的SQL*******************     DECLARE @SQL_INSERT VARCHAR(MAX)=‘‘    INSERT INTO ‘[email protected]+‘ SELECT ‘‘[email protected]+‘‘ FROM BACKUP_‘[email protected]+‘ WHERE UPDATEGUID=‘‘‘‘‘‘[email protected]+‘‘‘‘‘‘ AND UpdateType=‘‘‘‘DELETE‘‘‘‘    ‘‘     --*********************還原作業之後把備份表和log表的記錄刪掉*************     DECLARE @SQL_DELGUID VARCHAR(MAX)=‘‘    DELETE FROM BACKUP_‘[email protected]+‘ WHERE  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 ‘‘‘‘復原操作執行成功，共恢複 ‘‘+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=‘‘‘‘‘‘[email protected]+‘‘‘‘‘‘      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(),‘‘‘[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復原恢複誤操作的資料