MSSQL DELETE 복원

어느 날 실시간으로 트랜잭션 로그에 있는 데이터를 확인 할 수 있는 프로그램을 봄

그래서 검색해보다가(과거 DB 날린 기억..)

Code Project에서 어떤 쿼리를 보게돼서 신세계다! 하고 참고해서 개발하다가 어려워서 포기함

(수정하다 포기. 미완성. 근데 뭐가 미완성인지 모름.. 기억이 안나요. 데이터 타입도 덜 추가했고 또 뭐 있더라..)

 

테이블 하나 만들어놓고 데이터 대충 몇 개 넣고 삭제한 다음에 해보면 잘 되긴 하는데..

운영 데이터는 어마어마하게 많을 거고...조회 엄청 느려서 그냥 포기 했던 거 같음

아래 프로시저가 2개로 나뉘어 있는데

첫번째 프로시저에서 Delete 된 Data의 Begin Time(트랜잭션 실행시간)을 유추해서

Where절 조건에 넣을 수 있다면 조금 더 빠를듯?

 

Update도 만들었던 거 같은데 왜 없지....ㅠㅠ

 

 

 

-- 2019 10 15 jhhyun
-- SQL 2005 이상만 가능
-- DELETE 전용
-- Database Mode가 FULL이여야 함(SIMPLE은 이거로 복구 못해~~ ㅅㄱ!)
 
 
-- DELETED_RECOVERY_STEP_1 프로시저를 실행 시키면
-- 트랜잭션 실행 시간 컬럼(Begin Time)
-- 복구하려는 binary 데이터 컬럼(RowLog Contents 0)
-- 위 두개의 컬럼이 존재하는데 delete를 실행한 시간대를 유추해서
-- 맞는 트랜잭션의 RowLog Contents 0 값을 
-- DELETED_RECOVERY_STEP_2 프로시저에 넣어서 실행
 
-- 실행 예는 아래와 같음
--EXEC dbo.DELETED_RECOVERY_STEP_1 'content'
--위 결과에서 내가 복구하려는 RowLog Contents 0 의 값이 0x3000080005000000040000030016001C002200616C6C636F5F32303304D6FCC841B1 일 때 아래와 같이 실행
--EXEC dbo.DELETED_RECOVERY_STEP_2 'content',0x3000080005000000040000030016001C002200616C6C636F5F32303304D6FCC841B1
 
/******************************************************************************************************************************************
PROCEDURE DELETED_RECOVERY_STEP_1
******************************************************************************************************************************************/
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
SQL 2005 이상만 가능
*/
CREATE PROCEDURE [dbo].[DELETED_RECOVERY_STEP_1]
    @TableName NVARCHAR(100) 
AS
DECLARE @allocation_unit_id NVARCHAR(150)
 
SELECT 
    @allocation_unit_id = allocunits.allocation_unit_id
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions 
ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id)
OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id)
INNER JOIN sysobjects objects 
ON partitions.object_id = objects.id
AND objects.type IN ('U', 'u')
WHERE partitions.index_id IN (0, 1)
AND objects.name = @TableName
 
IF ISNULL(@allocation_unit_id,'') = ''
    BEGIN
        SELECT '테이블 명을 확인하세요.'
        RETURN;
    END
SELECT
    [Current LSN]
    , Operation
    , dblog.[Transaction ID]
    , AllocUnitId
    , AllocUnitName
    , [Page ID]
    , [Slot ID]
    , [Num Elements]
    , dblog1.[Begin Time]
    , dblog1.[Transaction Name]
    , [RowLog Contents 0]
    , [Log Record]
FROM fn_dblog(NULL, NULL) dblog
INNER JOIN 
( 
    SELECT 
        allocunits.allocation_unit_id
        , objects.name
        , objects.id
    FROM sys.allocation_units allocunits 
    INNER JOIN sys.partitions partitions 
    ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) 
    OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id) 
    INNER JOIN sysobjects objects 
    ON partitions.object_id = objects.id
    AND objects.type IN ('U', 'u')
    WHERE partitions.index_id IN (0, 1)
) allocunits 
ON dblog.AllocUnitID = allocunits.allocation_unit_id 
INNER JOIN 
(
    SELECT 
        [Begin Time]
        , [Transaction Name]
        , [Transaction ID]
    FROM fn_dblog(NULL, NULL) x
    WHERE Operation = 'LOP_BEGIN_XACT' 
) dblog1 
ON dblog1.[Transaction ID] = dblog.[Transaction ID]
WHERE [Page ID] IS NOT NULL 
AND [Slot ID] >= 0
AND dblog.[Transaction ID] != '0000:00000000' 
AND Context in ('LCX_HEAP', 'LCX_CLUSTERED')
AND AllocUnitId = @allocation_unit_id
AND dblog1.[Transaction Name] = 'DELETE'
 
 
/******************************************************************************************************************************************
PROCEDURE DELETED_RECOVERY_STEP_2
******************************************************************************************************************************************/
 
/****** Object:  StoredProcedure [dbo].[Recover_Inserted_Data_Proc]    Script Date: 2019-10-15 오후 5:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DELETED_RECOVERY_STEP_2]
    @TableName NVARCHAR(100)
    ,@RowLogContents VARBINARY(8000)
    
AS
DECLARE @allocation_unit_id NVARCHAR(150)
 
SELECT 
    @allocation_unit_id = allocunits.allocation_unit_id
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions 
ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id)
OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id)
INNER JOIN sysobjects objects 
ON partitions.object_id = objects.id
AND objects.type IN ('U', 'u')
WHERE partitions.index_id IN (0, 1)
AND objects.name = @TableName
 
DECLARE @lenFixedBytes SMALLINT
        , @noOfCols SMALLINT
        , @nullBitMapLength SMALLINT
        , @nullByteMap VARBINARY(MAX)
        , @nullBitMap VARCHAR(MAX)
        , @noVarCols SMALLINT
        , @columnOffsetArray VARBINARY(MAX)
        , @varColPointer SMALLINT
 
SELECT 
    @lenFixedBytes = CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, 2 + 1, 2)))),
    @noOfCols = CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 1, 2)))),
    @nullBitMapLength = CONVERT(INT, ceiling(@noOfCols/8.0)),
    @nullByteMap = SUBSTRING(@RowLogContents, @lenFixedBytes + 3, @nullBitMapLength),
    @noVarCols = 
                CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 
                    THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength, 2))))
                ELSE null END, 
    @columnOffsetArray = 
                CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 
                    THEN SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength + 2, @noVarCols * 2)
                ELSE null END,
    @varColPointer = 
                CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 
                    THEN (@lenFixedBytes + 2 + @nullBitMapLength + 2 + (@noVarCols * 2))
                ELSE null END
 
 
DECLARE @byteTable TABLE
(
    byte INT
)
DECLARE @cnt INT 
SET @cnt = 1
 
WHILE (@cnt < @nullBitMapLength + 1)
    BEGIN
        INSERT INTO @byteTable(byte) VALUES(@cnt)
 
        SET @cnt = @cnt +1
    END
 
SELECT 
    @nullBitMap = COALESCE(@nullBitMap, '')
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 128) % 2)
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 64) % 2) 
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 32) % 2) 
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 16) % 2) 
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 8) % 2)
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 4) % 2)
                 + CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 2) % 2)
                 + CONVERT(NVARCHAR(1), SUBSTRING(@nullByteMap, byte, 1) % 2) 
FROM @byteTable b 
ORDER BY byte DESC
 
DECLARE @colOffsetTable TABLE
(
    colNum SMALLINT,
    columnOffset VARBINARY(2),
    columnOffvalue SMALLINT,
    columnLength SMALLINT
)
 
SET @cnt = 1
 
WHILE (@cnt <= @noVarCols)
    BEGIN
        INSERT INTO @colOffsetTable (colNum, columnOffset, columnOffValue, columnLength)
        VALUES(
            (@cnt * (- 1))
            ,SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2)
            ,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2))))
            ,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2))))
            - ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * (@cnt - 1)) - 1, 2)))), 0), @varColPointer)
        )
 
        SET @cnt = @cnt + 1
    END
 
 
DECLARE @schema TABLE 
(
    [column] INT,
    [length] INT,
    [name] NVARCHAR(255),
    [system_type_id] INT,
    [bitpos] INT,
    [xprec] INT,
    [xscale] INT,
    [leaf_offset] INT,
    [is_uniqueifier] BIT,
    [is_null] BIT NULL
)
 
 
INSERT INTO @schema
SELECT
    cols.leaf_null_bit AS nullbit
    ,ISNULL(syscolumns.length, cols.max_length) AS [length]
    ,CASE WHEN is_uniqueifier = 1 THEN 'UNIQUIFIER'
    ELSE isnull(syscolumns.name, 'DROPPED') END [name]
    ,cols.system_type_id
    ,cols.leaf_bit_position AS bitpos
    ,ISNULL(syscolumns.xprec, cols.precision) AS xprec
    ,ISNULL(syscolumns.xscale, cols.scale) AS xscale
    ,cols.leaf_offset
    ,is_uniqueifier
    ,SUBSTRING(REVERSE(@nullBitMap), cols.leaf_null_bit, 1) AS is_null
FROM sys.allocation_units allocunits 
INNER JOIN sys.partitions partitions 
ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) 
OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols 
ON cols.partition_id = partitions.partition_id
LEFT JOIN syscolumns 
ON syscolumns.id = partitions.object_id 
AND syscolumns.colid = cols.partition_column_id
WHERE allocunits.allocation_unit_id = @allocation_unit_id
ORDER BY nullbit
 
 
INSERT INTO @schema
SELECT -3, 1, 'StatusBitsA', 0, 0, 0, 0, 2147483647, 0, 0
 
INSERT INTO @schema
SELECT -2, 1, 'StatusBitsB', 0, 0, 0, 0, 2147483647, 0, 0
 
INSERT INTO @schema
SELECT -1, 2, 'LenFixedBytes', 52, 0, 10, 0, 2147483647, 0, 0
 
 
SELECT
    [name] AS ColumnName
    ,CASE WHEN s.is_null = 1 
        THEN NULL 
    ELSE 
        --변수의 타입에 따라 binary 데이터를 가공해야함
        --ex) system_type_id가 239는 varchar, 231은 nvarchar
        CASE 
            --2019 10 15 HJH 수정
            WHEN s.system_type_id IN (167, 175) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), REVERSE(REVERSE(REPLACE(hex_string, 0x00, 0x20))))))
            --2019 10 15 HJH 수정
            WHEN s.system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_string)  )) 
            WHEN s.system_type_id = 48 THEN CONVERT(NVARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_string))))
            WHEN s.system_type_id = 52 THEN CONVERT(NVARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_string))))
            WHEN s.system_type_id = 56 THEN CONVERT(NVARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_string))))
            WHEN s.system_type_id = 127 THEN CONVERT(NVARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_string))))
            WHEN s.system_type_id = 61 THEN 
            CONVERT(VARCHAR(MAX), 
            CONVERT(DATETIME, SUBSTRING(hex_string, 4, 1) + SUBSTRING(hex_string, 3, 1) + SUBSTRING(hex_string, 2, 1) + SUBSTRING(hex_string, 1, 1))
            + CONVERT(DATETIME, DATEADD(dd, CONVERT(INT, SUBSTRING(hex_string, 8, 1) + SUBSTRING(hex_string, 7, 1) + SUBSTRING(hex_string, 6, 1) + SUBSTRING(hex_string, 5, 1)), 0x00000000))
            , 109)
            WHEN s.system_type_id = 108 AND s.xprec = 5 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(5,2), 0x050200 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(6,2), 0x060200 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 3 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(6,3), 0x060300 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 7 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(7,2), 0x070200 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 8 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(8,2), 0x080200 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 9 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(9,2), 0x090200 + hex_string))
            WHEN s.system_type_id = 108 AND s.xprec = 10 AND s.xscale = 2 THEN CONVERT(NVARCHAR(MAX), CONVERT(NUMERIC(10,2), 0x0A0200 + hex_string))
        END
    END AS ClearText
FROM 
(
        SELECT 
            s.name
            ,s.is_null
            ,s.[column]
            ,s.is_uniqueifier
            ,s.system_type_id
            ,s.xprec
            ,s.xscale
            ,CASE WHEN s.leaf_offset > 1 AND s.bitpos = 0 
                THEN SUBSTRING (
                                @RowLogContents, 
                                ISNULL((SELECT TOP 1 SUM(x.length) FROM @schema x WHERE x.[column] < s.[column] AND x.leaf_offset > 1 AND x.bitpos = 0), 0) + 1,
                                s.LENGTH)
            ELSE SUBSTRING (
                            @RowLogContents, 
                            (col.columnOffValue - col.columnLength) + 1,
                            col.columnLength)
            END AS hex_string
    FROM @schema s
    LEFT JOIN @colOffsetTable col 
    ON col.colNum = s.leaf_offset
) AS s
WHERE [column] > 0 
AND is_uniqueifier = 0