Post
Ask Question

Recover Deleted Data from Table without using Database Backup in SQL Server

  1. CREATE PROC [dbo].[uspRecoverDeletedData]( @TableName AS VARCHAR(100)='tbl_emp',   
  2. @SchemaName AS                                           VARCHAR(50) = 'dbo',   
  3. @btAllTransaction AS                                     TINYINT = 1 )   
  4. AS   
  5.   BEGIN   
  6.     SET nocount ON   
  7.     DECLARE @DataLog AS          VARCHAR(max)   
  8.     DECLARE @ColLength AS        VARCHAR(max) = ''   
  9.     DECLARE @ColName AS          VARCHAR(max) = ''   
  10.     DECLARE @TypeID AS           VARCHAR(max) = ''   
  11.     DECLARE @Query AS            NVARCHAR(max)   
  12.     DECLARE @vcAllocationUnit AS VARCHAR(200)   
  13.     DECLARE @VarPosition AS      VARCHAR(500) = ''   
  14.     DECLARE @VarLength AS        VARCHAR(500) = ''   
  15.     DECLARE @VarLastPosition AS  VARCHAR(500) = ''   
  16.     DECLARE @BitData AS          VARCHAR(500) = ''   
  17.     DECLARE @vcTranID AS         VARCHAR(50)   
  18.     DECLARE @TotalLength AS      INTEGER = 5   
  19.     DECLARE @OffsetData AS       INTEGER   
  20.     DECLARE @BitLength AS        INTEGER   
  21.     DECLARE @BitCounter AS       INTEGER = 0   
  22.     DECLARE @VarCounter AS       INTEGER = 0   
  23.     DECLARE @TotalVar AS         INTEGER   
  24.     DECLARE @TotalColumn AS      INTEGER   
  25.     DECLARE @TotalNull AS        INTEGER   
  26.     DECLARE @Hex AS VARBINARY(20)   
  27.     DECLARE @btPrimaryKey AS BIT = 0   
  28.     BEGIN try   
  29.       SET @TableName = @SchemaName + '.' + @TableName   
  30.       IF Object_id(@TableName) IS NULL   
  31.       RAISERROR('Invalid table or schema name',16,100)   
  32.       SELECT @vcAllocationUnit = NAME   
  33.       FROM   sys.key_constraints   
  34.       WHERE  parent_object_id = Object_id(@TableName)   
  35.       AND    [Type] = 'PK'   
  36.       IF @vcAllocationUnit IS NULL   
  37.       BEGIN   
  38.         SET @vcAllocationUnit = @TableName   
  39.         SET @btPrimaryKey = 0   
  40.       END   
  41.       ELSE   
  42.       BEGIN   
  43.         SET @vcAllocationUnit = @TableName + '.' + @vcAllocationUnit   
  44.         SET @btPrimaryKey = 1   
  45.       END   
  46.       SET @BitLength = ((   
  47.       (   
  48.              SELECT Count(*)   
  49.              FROM   syscolumns   
  50.              WHERE  id = Object_id(@TableName)   
  51.              AND    xusertype = 104)-1))/8 + 1   
  52.       SET @BitLength =   
  53.       CASE   
  54.       WHEN @BitLength = -1 THEN   
  55.         0   
  56.         ELSE @BitLength   
  57.       END   
  58.       SET @TotalColumn =   
  59.       (   
  60.              SELECT Count(*)   
  61.              FROM   syscolumns   
  62.              WHERE  id = Object_id(@TableName))   
  63.       SET @TotalNull =   
  64.       CASE   
  65.       WHEN @TotalNull % 8 = 0 THEN   
  66.         @TotalColumn     /8   
  67.         ELSE @TotalColumn/8 + 1   
  68.       END   
  69.       SET @TotalVar =   
  70.       (   
  71.                  SELECT     Count(*)   
  72.                  FROM       syscolumns C   
  73.                  INNER JOIN systypes T   
  74.                  ON         c.xusertype = t.xusertype   
  75.                  WHERE      id = Object_id(@TableName)   
  76.                  AND        variable = 1)   
  77.       IF @btPrimaryKey = 1   
  78.       BEGIN   
  79.         SELECT TOP(1)   
  80.                @OffsetData = Cast(Cast(Reverse(Substring([RowLog Contents 0],3,2)) AS BINARY(2)) AS INT)  
  81.         FROM   sys.Fn_dblog(NULL,NULL)   
  82.         WHERE  allocunitname = @vcAllocationUnit   
  83.         AND    operation = 'LOP_DELETE_ROWS'   
  84.         AND    context = 'LCX_MARK_AS_GHOST'   
  85.       END   
  86.       ELSE   
  87.       BEGIN   
  88.         SELECT TOP(1)   
  89.                @OffsetData = Cast(Cast(Reverse(Substring([RowLog Contents 0],3,2)) AS BINARY(2)) AS INT)  
  90.         FROM   sys.Fn_dblog(NULL,NULL)   
  91.         WHERE  allocunitname = @vcAllocationUnit   
  92.         AND    operation = 'LOP_DELETE_ROWS'   
  93.         AND    context = 'LCX_HEAP'   
  94.       END   
  95.       SET @Query = 'SELECT '   
  96.       SELECT     @BitCounter +=   
  97.                  CASE   
  98.                             WHEN c.xusertype IN(104) THEN 1   
  99.                             ELSE 0   
  100.                  END,   
  101.                  @VarCounter +=   
  102.                  CASE   
  103.                             WHEN variable = 1 THEN 1   
  104.                             ELSE 0   
  105.                  END,   
  106.                  @BitData =   
  107.                  CASE   
  108.                             WHEN @BitCounter = 1   
  109.                             AND        c.xusertype IN(104) THEN 'SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(@BitLength AS VARCHAR) + ')'  
  110.                             ELSE @BitData   
  111.                  END,   
  112.                  @VarPosition =   
  113.                  CASE   
  114.                             WHEN @VarCounter = 1 THEN Cast((@TotalLength + 4 + (@TotalVar * 2 ) + @TotalNull) AS VARCHAR)  
  115.                             WHEN @VarCounter > 1 THEN @VarPosition + ' + ' + @VarLength   
  116.                  END,   
  117.                  @VarLastPosition =   
  118.                  CASE   
  119.                             WHEN @VarCounter = 1 THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast((@TotalLength + 4 + + @TotalNull + ((@VarCounter - 1) * 2)) AS VARCHAR) + ' ,2)) AS BINARY(2)) AS INT)'  
  120.                             WHEN @VarCounter > 1 THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast((@TotalLength + 4 + + @TotalNull + ((@VarCounter - 2) * 2)) AS VARCHAR) + ' ,2)) AS BINARY(2)) AS INT)'  
  121.                             ELSE ''   
  122.                  END,   
  123.                  @VarLength =   
  124.                  CASE   
  125.                             WHEN @VarCounter = 1 THEN @VarLastPosition                                   + ' - ( ' + Cast((4 + (@TotalVar * 2) + @TotalNull + @OffsetData) AS  VARCHAR) + ')'  
  126.                             WHEN @VarCounter > 1 THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast((@TotalLength + 4 + + @TotalNull + ((@VarCounter - 1) * 2)) AS VARCHAR) + ' ,2)) AS BINARY(2)) AS INT) - ' + @VarLastPosition  
  127.                             ELSE ''   
  128.                  END,   
  129.                  @Query +=   
  130.                  CASE   
  131.                             WHEN c.xusertype IN(175,62) THEN'RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8)))))) '   
  132.                             WHEN c.xusertype IN(59) THEN'LEFT(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS               VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8)'  
  133.                             WHEN c.xusertype IN(108,106) THEN 'CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(38,30), CONVERT(VARBINARY,CONVERT(VARBINARY,' + Cast(c.xprec AS                        VARCHAR) + ')+CONVERT(VARBINARY,' + Cast(c.xscale AS VARCHAR) + '))+CONVERT(VARBINARY(1),0) + CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8))) as FLOAT))'   
  134.                             WHEN c.xusertype IN(40,58,61,42,60,122) THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS                                               VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8)) AS ' + Type_name(c.xusertype) + ') '   
  135.                             WHEN c.xusertype IN(36) THEN 'CAST(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS                                                                            VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ') AS ' + Type_name(c.xusertype) + ') '   
  136.                             WHEN c.xusertype IN(165,173) THEN 'SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS                                                                            VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ') '   
  137.                             WHEN c.xusertype IN(41,43) THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS                                                            VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(8)) AS ' + Type_name(c.xusertype) + '(' + Cast(c.[Length] AS VARCHAR) + ')) '   
  138.                             WHEN c.xusertype IN(56,127,52,48) THEN 'CAST(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS                                                     VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ')) AS BINARY(' + Cast(c.length AS VARCHAR) + ')) AS ' + Type_name(c.xusertype) + ')'  
  139.                             WHEN c.xusertype IN(104) THEN 'CASE WHEN CAST(REVERSE(' + @BitData + ') AS BINARY(' + Cast(@BitLength AS                                                             VARCHAR) + ')) & ' + Cast(Power(2,@BitCounter-1) AS VARCHAR) + '= ' + Cast(Power(2,@BitCounter-1) AS VARCHAR) + 'THEN 1 ELSE 0 END'   
  140.                             WHEN c.xusertype IN(165) THEN 'REVERSE(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + '))) AS VARCHAR(MAX)))'   
  141.                             WHEN c.xusertype IN(167) THEN 'CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + ')),4)'   
  142.                             WHEN c.xusertype IN(231) THEN 'CONVERT(NVARCHAR(MAX), SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + ')),4)'   
  143.                  END                          + ' AS ' + Quotename(c.NAME) + ' ,',   
  144.                  @TotalLength                 +=   
  145.                  CASE   
  146.                             WHEN c.xusertype IN(104)   
  147.                             AND        @BitCounter = 1 THEN @BitLength   
  148.                             WHEN c.xusertype IN(104)   
  149.                             AND        @BitCounter > 1 THEN 0   
  150.                             WHEN variable = 1 THEN 0   
  151.                             ELSE c.[Length]   
  152.                  END   
  153.       FROM       syscolumns C   
  154.       INNER JOIN systypes T   
  155.       ON         c.xusertype = t.xusertype   
  156.       WHERE      id = Object_id(@TableName)   
  157.       ORDER BY   variable,   
  158.                  colorder   
  159.       IF @btPrimaryKey = 1   
  160.       BEGIN   
  161.         SET @Query = LEFT(@Query,Len(@Query) - 1) + 'FROM Sys.fn_dblog(NULL,NULL) WHERE AllocUnitName = ''' + @vcAllocationUnit + ''' AND Operation = ''LOP_DELETE_ROWS'' AND Context = ''LCX_MARK_AS_GHOST'''   
  162.         IF @btAllTransaction = 0   
  163.         SET @vcTranID =   
  164.         (   
  165.                SELECT Max([Transaction ID])   
  166.                FROM   sys.Fn_dblog(NULL,NULL) WHEREAllocUnitName = @vcAllocationUnit   
  167.                AND    operation = 'LOP_DELETE_ROWS'   
  168.                AND    context = 'LCX_MARK_AS_GHOST')   
  169.       END   
  170.       ELSE   
  171.       BEGIN   
  172.         SET @Query = LEFT(@Query,Len(@Query) - 1) + 'FROM Sys.fn_dblog(NULL,NULL) WHERE AllocUnitName = ''' + @vcAllocationUnit + ''' AND Operation = ''LOP_DELETE_ROWS'' AND Context = ''LCX_HEAP'''   
  173.         IF @btAllTransaction = 0   
  174.         SET @vcTranID =   
  175.         (   
  176.                SELECT Max([Transaction ID])   
  177.                FROM   sys.Fn_dblog(NULL,NULL) WHEREAllocUnitName = @vcAllocationUnit   
  178.                AND    operation = 'LOP_DELETE_ROWS'   
  179.                AND    context = 'LCX_HEAP')   
  180.       END   
  181.       IF @btAllTransaction = 0   
  182.       BEGIN   
  183.         SET @Query += ' AND [Transaction ID] = '''+ @vcTranID + ''''   
  184.       END   
  185.       EXECUTE sp_executesql   
  186.         @Query   
  187.     END try   
  188.     BEGIN catch   
  189.       SELECT Error_message()   
  190.     END catch   
  191.   ENDEXEC Usprecoverdeleteddata