sqlsever回滚操作_SQL SERVER回滚恢复误操作的数据

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 TABLEUPDATE_LOG

(

UpdateGUIDVARCHAR(36),

UpdateTimeDATETIME,

TableNamevarchar(20),

UpdateTypevarchar(6),

RollBackSQLvarchar(MAX),

ExecSQLVARCHAR(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 FROMINFORMATION_SCHEMA.columnsWHERE TABLE_NAME=@TABLENAME

OPENtest_CursorDECLARE @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