CREATE PROC [dbo].[uspRecoverDeletedData]( @TableName AS VARCHAR(100)='tbl_emp',
@SchemaName AS VARCHAR(50) = 'dbo',
@btAllTransaction AS TINYINT = 1 )
AS
BEGIN
SET nocount ON
DECLARE @DataLog AS VARCHAR(max)
DECLARE @ColLength AS VARCHAR(max) = ''
DECLARE @ColName AS VARCHAR(max) = ''
DECLARE @TypeID AS VARCHAR(max) = ''
DECLARE @Query AS NVARCHAR(max)
DECLARE @vcAllocationUnit AS VARCHAR(200)
DECLARE @VarPosition AS VARCHAR(500) = ''
DECLARE @VarLength AS VARCHAR(500) = ''
DECLARE @VarLastPosition AS VARCHAR(500) = ''
DECLARE @BitData AS VARCHAR(500) = ''
DECLARE @vcTranID AS VARCHAR(50)
DECLARE @TotalLength AS INTEGER = 5
DECLARE @OffsetData AS INTEGER
DECLARE @BitLength AS INTEGER
DECLARE @BitCounter AS INTEGER = 0
DECLARE @VarCounter AS INTEGER = 0
DECLARE @TotalVar AS INTEGER
DECLARE @TotalColumn AS INTEGER
DECLARE @TotalNull AS INTEGER
DECLARE @Hex AS VARBINARY(20)
DECLARE @btPrimaryKey AS BIT = 0
BEGIN try
SET @TableName = @SchemaName + '.' + @TableName
IF Object_id(@TableName) IS NULL
RAISERROR('Invalid table or schema name',16,100)
SELECT @vcAllocationUnit = NAME
FROM sys.key_constraints
WHERE parent_object_id = Object_id(@TableName)
AND [Type] = 'PK'
IF @vcAllocationUnit IS NULL
BEGIN
SET @vcAllocationUnit = @TableName
SET @btPrimaryKey = 0
END
ELSE
BEGIN
SET @vcAllocationUnit = @TableName + '.' + @vcAllocationUnit
SET @btPrimaryKey = 1
END
SET @BitLength = ((
(
SELECT Count(*)
FROM syscolumns
WHERE id = Object_id(@TableName)
AND xusertype = 104)-1))/8 + 1
SET @BitLength =
CASE
WHEN @BitLength = -1 THEN
0
ELSE @BitLength
END
SET @TotalColumn =
(
SELECT Count(*)
FROM syscolumns
WHERE id = Object_id(@TableName))
SET @TotalNull =
CASE
WHEN @TotalNull % 8 = 0 THEN
@TotalColumn /8
ELSE @TotalColumn/8 + 1
END
SET @TotalVar =
(
SELECT Count(*)
FROM syscolumns C
INNER JOIN systypes T
ON c.xusertype = t.xusertype
WHERE id = Object_id(@TableName)
AND variable = 1)
IF @btPrimaryKey = 1
BEGIN
SELECT TOP(1)
@OffsetData = Cast(Cast(Reverse(Substring([RowLog Contents 0],3,2)) AS BINARY(2)) AS INT)
FROM sys.Fn_dblog(NULL,NULL)
WHERE allocunitname = @vcAllocationUnit
AND operation = 'LOP_DELETE_ROWS'
AND context = 'LCX_MARK_AS_GHOST'
END
ELSE
BEGIN
SELECT TOP(1)
@OffsetData = Cast(Cast(Reverse(Substring([RowLog Contents 0],3,2)) AS BINARY(2)) AS INT)
FROM sys.Fn_dblog(NULL,NULL)
WHERE allocunitname = @vcAllocationUnit
AND operation = 'LOP_DELETE_ROWS'
AND context = 'LCX_HEAP'
END
SET @Query = 'SELECT '
SELECT @BitCounter +=
CASE
WHEN c.xusertype IN(104) THEN 1
ELSE 0
END,
@VarCounter +=
CASE
WHEN variable = 1 THEN 1
ELSE 0
END,
@BitData =
CASE
WHEN @BitCounter = 1
AND c.xusertype IN(104) THEN 'SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(@BitLength AS VARCHAR) + ')'
ELSE @BitData
END,
@VarPosition =
CASE
WHEN @VarCounter = 1 THEN Cast((@TotalLength + 4 + (@TotalVar * 2 ) + @TotalNull) AS VARCHAR)
WHEN @VarCounter > 1 THEN @VarPosition + ' + ' + @VarLength
END,
@VarLastPosition =
CASE
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)'
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)'
ELSE ''
END,
@VarLength =
CASE
WHEN @VarCounter = 1 THEN @VarLastPosition + ' - ( ' + Cast((4 + (@TotalVar * 2) + @TotalNull + @OffsetData) AS VARCHAR) + ')'
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
ELSE ''
END,
@Query +=
CASE
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)))))) '
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)'
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))'
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) + ') '
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) + ') '
WHEN c.xusertype IN(165,173) THEN 'SUBSTRING([RowLog Contents 0],' + Cast(@TotalLength AS VARCHAR) + ',' + Cast(c.[Length] AS VARCHAR) + ') '
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) + ')) '
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) + ')'
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'
WHEN c.xusertype IN(165) THEN 'REVERSE(CAST(REVERSE(SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + '))) AS VARCHAR(MAX)))'
WHEN c.xusertype IN(167) THEN 'CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + ')),4)'
WHEN c.xusertype IN(231) THEN 'CONVERT(NVARCHAR(MAX), SUBSTRING([RowLog Contents 0],' + @VarPosition + ',(' + @VarLength + ')),4)'
END + ' AS ' + Quotename(c.NAME) + ' ,',
@TotalLength +=
CASE
WHEN c.xusertype IN(104)
AND @BitCounter = 1 THEN @BitLength
WHEN c.xusertype IN(104)
AND @BitCounter > 1 THEN 0
WHEN variable = 1 THEN 0
ELSE c.[Length]
END
FROM syscolumns C
INNER JOIN systypes T
ON c.xusertype = t.xusertype
WHERE id = Object_id(@TableName)
ORDER BY variable,
colorder
IF @btPrimaryKey = 1
BEGIN
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'''
IF @btAllTransaction = 0
SET @vcTranID =
(
SELECT Max([Transaction ID])
FROM sys.Fn_dblog(NULL,NULL) WHEREAllocUnitName = @vcAllocationUnit
AND operation = 'LOP_DELETE_ROWS'
AND context = 'LCX_MARK_AS_GHOST')
END
ELSE
BEGIN
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'''
IF @btAllTransaction = 0
SET @vcTranID =
(
SELECT Max([Transaction ID])
FROM sys.Fn_dblog(NULL,NULL) WHEREAllocUnitName = @vcAllocationUnit
AND operation = 'LOP_DELETE_ROWS'
AND context = 'LCX_HEAP')
END
IF @btAllTransaction = 0
BEGIN
SET @Query += ' AND [Transaction ID] = '''+ @vcTranID + ''''
END
EXECUTE sp_executesql
@Query
END try
BEGIN catch
SELECT Error_message()
END catch
ENDEXEC Usprecoverdeleteddata
Recover Deleted Data from Table without using Database Backup in SQL Server
This code snipper for Recover deleted data from table without using database backup in SQL server.