SQL Server Portal

How to recover deleted data from SQL Server 2000

January 23, 2012 by Muhammad Imran

In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2000.(Note: This script can recover following data types & compatible with CS collation).

tinyint
smallint
int
bigint
bit
char
varchar
nchar
nvarchar
datetime
smalldatetime
money
smallmoney
decimal
numeric
real
float
binary
varbinary
uniqueidentifier

Let me explain this issue demonstrating simple example.

--Create Table
CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student name] [varchar](50) NOT NULL,
[Date of Birth]  datetime not null,
[Grade] [char] (4) not null,
[Marks] [int] NULL)

--Inserting data into table
Insert into dbo.[Student] values (1,'Bob jerry','1974-12-31','VI',89)

--Check the existence of the data
Select * from dbo.[Student]

--Delete the record
Delete from dbo.[Student]

--Verify the data has been deleted
Select * from dbo.[Student]

Now, you need to create this procedure to recover your deleted data

CREATE PROCEDURE Recover_Deleted_Data_Proc
@SchemaName_n_TableName NVARCHAR(Max),
@Date_From datetime='1900/01/01',
@Date_To datetime ='9999/12/31'
as

DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(Max)
DECLARE @AllocUnitID BIGINT
Declare @AllocUnitName NVARCHAR(Max)
Declare @SQL NVARCHAR(Max)

DECLARE @bitTable TABLE
(
[ID] INT,
[Bitvalue] INT
)
--Create table to set the bit position of one byte.

INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128

--Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
[RowLogContents]	VARBINARY(8000),
[AllocUnitID]		BIGINT,
[Transaction ID]	NVARCHAR(Max),
[FixedLengthData]	SMALLINT,
[TotalNoOfCols]		SMALLINT,
[NullBitMapLength]	SMALLINT,
[NullBytes]			VARBINARY(8000),
[TotalNoofVarCols]	SMALLINT,
[ColumnOffsetArray]	VARBINARY(8000),
[VarColumnStart]	SMALLINT,
[NullBitMap]		VARCHAR(MAX)
)
--Create a common table expression to get all the row data plus how many bytes we have for each row.
;WITH RowData AS (
SELECT

[RowLog Contents 0] AS [RowLogContents]

,[AllocUnitID] AS [AllocUnitID]

,[Transaction ID] AS [Transaction ID]

--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData

-- [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as  [TotalNoOfCols]

--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]

--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols]

--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)
* 2)  ELSE null  END) AS [ColumnOffsetArray]

--	Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
THEN  (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))

+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2))

ELSE null End AS [VarColumnStart]

FROM    sys.fn_dblog(NULL, NULL)
WHERE    AllocUnitName ='' + @SchemaName_n_TableName + '' --'dbo.Student'
AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)

/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
Where Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DELETE'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

--Use this technique to repeate the row till the no of bytes of the row.
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

insert into @DeletedRecords
Select	RowLogContents
,[AllocUnitID]
,[Transaction ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
---Get the Null value against each column (1 means null zero means not null)
,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]
FROM N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
FROM RowData D

CREATE TABLE [#temp_Data]
(
[FieldName]  VARCHAR(MAX),
[FieldValue] VARCHAR(MAX),
[Rowlogcontents] VARBINARY(8000)

)
--Create common table expression and join it with the rowdata table
-- to get each column details
;With CTE AS (
/*This part is for variable data columns*/
SELECT Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
--Calculate the variable column size from the variable column offset array
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],

---Calculate the column length
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END) AS [Column Length]

--Get the Hexa decimal value from the RowlogContent
--HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])
--This is the data of your column but in the Hexvalue
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END)
- ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) END AS hex_Value

FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
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 OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset<0

UNION
/*This part is for fixed data columns*/
SELECT  Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
syscolumns.length AS [Column Length]

,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
,syscolumns.length) END AS hex_Value

FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
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 OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0 )

--Converting data from Hexvalue to its orgional datatype.
--Implemented datatype conversion mechanism for each datatype

INSERT INTO #temp_Data
SELECT NAME,
CASE
WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))  --NVARCHAR ,NCHAR
WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))  --VARCHAR,CHAR
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(38,30), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- NUMERIC
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- DECIMAL
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))  -- BIT
WHEN system_type_id =62 THEN  RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
When system_type_id =59 THEN  Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER

END AS FieldValue
,[Rowlogcontents]
FROM CTE ORDER BY nullbit

--Create the column name in the same order to do pivot table.

SET @SQL = STUFF(
(
SELECT ', (Case when [FieldName]=''' + CAST([Name] AS VARCHAR(MAX)) + ''' Then [FieldValue] else NULL End) AS ' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX))  FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
FOR XML PATH('')), 1, 1, '')

DECLARE @FieldNameMin VARCHAR(max)
SET @FieldNameMin = STUFF(
(
SELECT ', Min(' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) + ') AS ' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
FOR XML PATH('')), 1, 1, '')

--Finally did pivot table and get the data back in the same format.

Set @SQL=';WITH CTE AS (SELECT ' + @SQL + ',[Rowlogcontents] as [Rowlogcontents] FROM #temp_Data) SELECT ' + @FieldNameMin + ' FROM CTE GROUP BY [Rowlogcontents]'

EXEC  (@SQL)

GO
--Execute the procedure like
--Recover_Deleted_Data_Proc 'Schema.table name'
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc 'dbo.Student'

--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc 'dbo.Student' ,'2011/12/23','2011/12/25'

--It will give you the result of all deleted records.

Explanation:

How does it work? Let’s go through it step by step. The process requires seven easy steps:

Step-1:

We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But, we need only the selected deleted records from the transaction log. So we included three filters (Context, Operation & AllocUnitName).

Context (‘LCX_MARK_AS_GHOST’and ‘LCX_HEAP’)
Operation (‘LOP_DELETE_ROWS’)
AllocUnitName(‘dbo.Student’) –- Schema + table Name

Here is the code snippet:

Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)WHERE AllocUnitName =‘dbo.Student’ AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)

This query will return number of columns providing different information, but we only need to select the column “RowLog content o, to get the deleted data.

The Column “RowLog content 0″ will look like this:

“0x300018000100000000000000006B0000564920205900000

00500E001002800426F62206A65727279″

Step-2:

Now,we have deleted data but in Hex values but SQL keeps this data in a specific sequence so we can easily recover it.But before recovering the data we need to understand the format. This format is defined in detail in Kalen Delaney’s SQL Internal’s book.

1 Byte : Status Bit A
1 Byte : Status Bit B
2 Bytes : Fixed length size
n Bytes : Fixed length data
2 Bytes : Total Number of Columns
n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)
2 Bytes : Number of variable-length columns
n Bytes : Column offset array (2x variable length column)
n Bytes : Data for variable length columns

So, the Hex data“RowLog content 0″ is equal to:

“Status Bit A + Status Bit B + Fixed length size + Fixed length data + Total Number of Columns + NULL Bitmap + Number of variable-length columns + NULL Bitmap+ Number of variable-length columns + Column offset array + Data for variable length columns.”

Step-3:

Now, we need to break the RowLog Content o (Hex value of our deleted data) into the above defined structure.[Color codes are used for reference only]

[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
[Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
[Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)
[Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] )
Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )
Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)

Step-4:

Now, we have the split of data as well,so we can find that which one column value is null or not by using Null Bytes. To achieve this convert Null Bytes (Hex value) into Binary format (As discussed, 1 indicates null for the column and 0 means there is some data).Here in this data, the Null Bitmap values are 00000111.We have only five column in student table (used as sample) and first five value of null bitmap is 00000.It means there is no null values.

Step-5:

Now, we have the primary data split (Step-3) and null values (Step-4) as well. After that we need to use this code snippet to get the column data like column name, column size, precision, scale and most importantly the leaf null bit (to ensure that the column is fixed data (<=-1) or variable data sizes (>=1)) of the table.

Select * 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 OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

And join it with our collected data table (Step-1,2,3,4) on the basis of allocunits.[Allocation_Unit_Id].Till now we know the information about the table and data,so we need to utilize this data to break [RowLog Contents 0] into table column data but in hex value. Here we need to take care as the data is either in fixed column size or in variable column size. .

Step-6:

We collected data in hex value (Step-5) with respect to each column. Now we need to convert the data with respect to its data type defined as [System_type_id]. Each type is having different mechanism
for data conversion.


--NVARCHAR ,NCHAR
WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))

--VARCHAR,CHAR
WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))

--TINY INTEGER
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value))))

--SMALL INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value))))

-- INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value))))

-- BIG INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))

--DATETIME
WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100)

--SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME

--- NUMERIC
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))

--MONEY,SMALLMONEY
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2)

--- DECIMAL
WHEN system_type_id = 106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), Convert(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))

-- BIT
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))

--- FLOAT
WHEN system_type_id = 62 THEN  RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value))))

--REAL
When  system_type_id =59 THEN  Left(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8)

--BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varbinary(max)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varchar(max)')

--UNIQUEIDENTIFIER
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))

Step-7:

Finally we do a pivot table over the data and you will see the result. THE DELETED DATA IS BACK.

Note: This data will only for display. It is not available in your selected table but you can insert this data in your table.

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Posted in Solutions, SQL SERVER | Tagged How to recover deleted data from SQL Sever | 9 Comments »

How to use multiple values for IN clause using same parameter (SQL Server)

December 21, 2011 by Muhammad Imran

I have been using IN clause with multiple values from a long time. But I was always using it with string concatenation along with single quotes to make it string and then process it.
Recently, I found another solution , it works without string concatenation.

Let me explain this issue demonstrating simple example.

CREATE TABLE [dbo].[Student](
[Student ID]   [varchar] (6) Not NULL ,
[Student Name] [varchar](50) NOT NULL)
GO

Insert into dbo.[Student] values ('STD001','Bob')
Insert into dbo.[Student] values ('STD002','Alexander')
Insert into dbo.[Student] values ('STD003','Hosanna')
Insert into dbo.[Student] values ('STD004','William')
Insert into dbo.[Student] values ('STD005','Hulda')
Insert into dbo.[Student] values ('STD006','Jacoba')

Old Approach :

Declare  @SQL         VARCHAR(MAX)
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'

SET @WhereClause=REPLACE(@WhereClause,',',''',''')
Set @SQL='Select *
from dbo.[Student]
Where  [Student Name] In (''' + @WhereClause + ''')'

EXEC (@SQL)

New Approach :

Declare  @Xml AS XML
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'
SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select *
from dbo.[Student]
Where  [Student Name] In (
SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
)

Explanation :

In the new approach, I need to explain these two important lines of code.

SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select @Xml

This code snippet will convert string into XML, replacing the delimiter with start and end XML tag.

(Here, It is used as <A></A>).

Output :

SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

Once the string is converted into XML,By using this code snippet, we can easily query it via xquery.
Output :

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Posted in Solutions, SQL SERVER | Tagged How to use multiple values for IN clause using same parameter | 16 Comments »

How to generate Insert statements from table data using SQL Server

December 20, 2011 by Muhammad Imran

Sometimes, we need to create insert into statements from a table (SQL Server) data for support ,testing or updating multiple instances etc. Given below stored procedure can generate the data from a table.
In this stored procedure, we will not create a traditional insert into … Values ().Instead of this statement we will use Insert into … select * …. The advantage is we can select the records before insertion to see what we will insert into the table.
(Note: This script can generate “insert into statement” for any table (SQL Server 2005 and above) having 296 or less columns & compatible with CS collation.
Given below are the datatype supported by this stored procedure).

tinyint
smallint
int
bigint
bit
char
varchar
nchar
nvarchar
datetime
smalldatetime
money
smallmoney
decimal
numeric
real
float
binary
varbinary
uniqueidentifier

This procedure will create different “insert into statement” for different scenarios.

-- For less than 500 records. We will use this statement.(If we use this statement for more than 500 records, It might reduce the performance.)
Insert into ...
Select .... Union ALL
Select .... Union ALL

-- For more than 500 records seperate queries for seperate select statements.
Insert into ...
Select ....

Other features of this scripts are :

You can use a where clause to generate only filtered data.
You can provide a top Number to filter a top number of records.

Let me explain this issue demonstrating simple example.

CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student ID] nvarchar(6) Not NULL ,
[Student name] [varchar](50) NOT NULL,
[Date of Birth]  datetime not null,
[Weight] [int] NULL)
GO

Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',70)
Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',80)
Insert into dbo.[Student] values (3,'STD003','Hosanna','2005-10-05',82)
Insert into dbo.[Student] values (4,'STD004','William','2006-09-03',55)
Insert into dbo.[Student] values (5,'STD005','Hulda','2007-08-01',35)
Insert into dbo.[Student] values (6,'STD006','Jacoba','2008-07-18',30)

Now, you need to create this procedure to generate “insert into statement” from table data.

CREATE PROCEDURE SAMPLE_DATA_PROC
@SchemaName VARCHAR(MAX),
@TableName VARCHAR(Max),
@WhereClause NVARCHAR(Max),
@TopNo INT
AS

DECLARE @SQL VARCHAR(max)
DECLARE @nSQL NVARCHAR(Max)
DECLARE @RecordCount INT
DECLARE @FirstColumn NVARCHAR(Max)

--SET @TableName=QUOTENAME(@TableName) -- Quote the table name
SET @TableName =@SchemaName + '.' + QUOTENAME(@TableName)
Print @TableName

/*We need to find the record count in order to remove Union ALL from the last row*/
SET @nSQL=''
SET @nSQL= N'SELECT @RecordCount=COUNT(*) FROM ' + @TableName + (CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' Where ' + @Whereclause ELSE '' END)
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N'@RecordCount INT OUTPUT', @RecordCount = @RecordCount OUTPUT
/*****************************************************************/

/*Need to check either top No of record is less than record count
in order to remove union all from the last row */
IF @TopNo IS NOT NULL And @TopNo<@RecordCount
BEGIN
SET @RecordCount=@TopNo
END
/***************************************************************/
/*** Here we need to find the first column name to generate a serial number and insert an "Insert into statement" in the first row ***/
SET @nSQL=''
SET @nSQL= N'SELECT @FirstColumn=[name] FROM sys.columns WHERE [Column_id]=1 And object_ID=object_ID('''+ @TableName + ''')'
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N'@FirstColumn nvarchar(Max) OUTPUT', @FirstColumn = @FirstColumn OUTPUT
/**************************************************************/

DECLARE @FieldName VARCHAR(max)
SET @FieldName=''

If (Select Count(*) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') And is_identity<>0)=1

BEGIN
SET @FieldName = STUFF(
(
SELECT ',' + QUOTENAME([Name]) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') Order By [column_id]
FOR XML PATH('')), 1, 1, '')
Set @FieldName ='(' + @FieldName + ')'
Print @FieldName
Print len(@FieldName)
END

/*******Create list of comma seperated columns *******/
SET @SQL= (SELECT STUFF((SELECT(CASE
WHEN system_type_id In (167,175,189) THEN + ' Cast(ISNULL(LTRIM(RTRIM(''N''''''+Replace(' + QUOTENAME([Name])+ ','''''''','''''''''''')+'''''''''+ ')),''NULL'') as varchar(max)) + '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
WHEN system_type_id In (231,239) THEN + ' Cast(ISNULL(LTRIM(RTRIM(''N''''''+Replace(' + QUOTENAME([Name])+ ','''''''','''''''''''')+'''''''''+ ')),''NULL'') as nvarchar(max)) + '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
WHEN system_type_id In (58,61,36) THEN + ' ISNULL(LTRIM(RTRIM(''N'''''' + Cast(' + QUOTENAME([Name])+ ' as varchar(max))+''''''''' + ')),''NULL'') + '' AS ' + QUOTENAME([Name]) + '''+ '' ,'''+' + '
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN + ' ISNULL(Cast(' + QUOTENAME([Name])+ ' as varchar(max)),''NULL'')+ '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
END
)
FROM
sys.columns WHERE object_ID=object_ID(''+ @TableName + '') FOR XML PATH('')),1,1,' '))

/*******************************************************/

/* Here 500 means if the record count is 500 or top no 500 then it will generate "Insert into select ..Union All "
Because more than 500 might reduce its performance. */
IF @TopNo <500 or @RecordCount<500
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl='SELECT (Case When ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' ''' + ' ELSE '''' END) + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') <>' + CONVERT(VARCHAR(10),@RecordCount) + ' THEN '' UNION ALL'' ELSE '''' END) AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
END
ELSE
BEGIN
SET @SQl= 'SELECT TOP ' + CONVERT(VARCHAR(10),@TopNo) + ' (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + '''' + ' ELSE '''' END) + ''SELECT ''+' + LEFT(@SQL,LEN(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') <>' + CONVERT(VARCHAR(10),@RecordCount) + ' THEN '' Union All'' ELSE '''' END) AS [DATA]' + ' FROM ' + @TableName + (CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
END
END
ELSE
--- Greator then 500 will generate "insert into select *" ... for each record.
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl='SELECT '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' '' + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
END
ELSE
BEGIN
SET @SQl='SELECT TOP ' + CONVERT(VARCHAR(10),@TopNo) + ''' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' '' + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
END
END
EXEC (@SQL)
GO

How to use this stored procedure


--Example 1 : When you need to generate "insert into statement" from all records within a table.
SAMPLE_DATA_PROC 'dbo','Student',NULL,NULL
GO
--Example 2 : When you need to generate "insert into statement" from top 5 records within a table.
SAMPLE_DATA_PROC 'dbo','Student',NULL,3
GO
--Example 3 : When you need to generate "insert into statement" with a specific filter (Single filter) within a table.
SAMPLE_DATA_PROC 'dbo','Student','[SNO]=1',NULL
GO
--Example 4 : When you need to generate "insert into statement" with a specific filter (Multiple filters) within a table.
SAMPLE_DATA_PROC 'dbo','Student','[SNO]=1  And [Student Name]=''Bob''',NULL
GO

If you execute example 1 : you will get all the date in the specified table like this

Now, you can comment “INSERT INTO [Student]” to view the data and uncomment it to insert data into the table.

-- INSERT INTO [Student]
SELECT 1 AS [Sno] ,N'STD001' AS [Student ID] ,N'Bob' AS [Student name] ,N'Dec 31 2003 12:00AM' AS [Date of Birth] ,70 AS [Weight] UNION ALL
SELECT 2 AS [Sno] ,N'STD002' AS [Student ID] ,N'Alexander' AS [Student name] ,N'Nov 15 2004 12:00AM' AS [Date of Birth] ,80 AS [Weight] UNION ALL
SELECT 3 AS [Sno] ,N'STD003' AS [Student ID] ,N'Hosanna' AS [Student name] ,N'Oct  5 2005 12:00AM' AS [Date of Birth] ,82 AS [Weight] UNION ALL
SELECT 4 AS [Sno] ,N'STD004' AS [Student ID] ,N'William' AS [Student name] ,N'Sep  3 2006 12:00AM' AS [Date of Birth] ,55 AS [Weight] UNION ALL
SELECT 5 AS [Sno] ,N'STD005' AS [Student ID] ,N'Hulda' AS [Student name] ,N'Aug  1 2007 12:00AM' AS [Date of Birth] ,35 AS [Weight] UNION ALL
SELECT 6 AS [Sno] ,N'STD006' AS [Student ID] ,N'Jacoba' AS [Student name] ,N'Jul 18 2008 12:00AM' AS [Date of Birth] ,30 AS [Weight]

If you have an identity column in the table, you need to add these two lines.

SET IDENTITY_INSERT Student ON  --(Before insert into statement)
GO
SET IDENTITY_INSERT Student OFF --(In the end of the statement)
GO

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Posted in Solutions, SQL SERVER | Tagged How to generate Insert statements from table data using SQL Server | 13 Comments »

How to recover deleted data from SQL Server

October 22, 2011 by Muhammad Imran

In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

image
text
uniqueidentifier
tinyint
smallint
int
smalldatetime
real
money
datetime
float
sql_variant
ntext
bit
decimal
numeric
smallmoney
bigint
varbinary
varchar
binary
char
timestamp
nvarchar
nchar
xml
sysname

Let me explain this issue demonstrating simple example.

--Create Table
Create Table [Test_Table]
(
[Col_image] image,
[Col_text] text,
[Col_uniqueidentifier] uniqueidentifier,
[Col_tinyint] tinyint,
[Col_smallint] smallint,
[Col_int] int,
[Col_smalldatetime] smalldatetime,
[Col_real] real,
[Col_money] money,
[Col_datetime] datetime,
[Col_float] float,
[Col_Int_sql_variant] sql_variant,
[Col_numeric_sql_variant] sql_variant,
[Col_varchar_sql_variant] sql_variant,
[Col_uniqueidentifier_sql_variant] sql_variant,
[Col_Date_sql_variant] sql_variant,
[Col_varbinary_sql_variant] sql_variant,
[Col_ntext] ntext,
[Col_bit] bit,
[Col_decimal] decimal(18,4),
[Col_numeric] numeric(18,4),
[Col_smallmoney] smallmoney,
[Col_bigint] bigint,
[Col_varbinary] varbinary(Max),
[Col_varchar] varchar(Max),
[Col_binary] binary(8),
[Col_char] char,
[Col_timestamp] timestamp,
[Col_nvarchar] nvarchar(Max),
[Col_nchar] nchar,
[Col_xml] xml,
[Col_sysname] sysname
)

GO
--Insert data into it
INSERT INTO [Test_Table]
([Col_image]
,[Col_text]
,[Col_uniqueidentifier]
,[Col_tinyint]
,[Col_smallint]
,[Col_int]
,[Col_smalldatetime]
,[Col_real]
,[Col_money]
,[Col_datetime]
,[Col_float]
,[Col_Int_sql_variant]
,[Col_numeric_sql_variant]
,[Col_varchar_sql_variant]
,[Col_uniqueidentifier_sql_variant]
,[Col_Date_sql_variant]
,[Col_varbinary_sql_variant]
,[Col_ntext]
,[Col_bit]
,[Col_decimal]
,[Col_numeric]
,[Col_smallmoney]
,[Col_bigint]
,[Col_varbinary]
,[Col_varchar]
,[Col_binary]
,[Col_char]
,[Col_nvarchar]
,[Col_nchar]
,[Col_xml]
,[Col_sysname])
VALUES
(CONVERT(IMAGE,REPLICATE('A',4000))
,REPLICATE('B',8000)
,NEWID()
,10
,20
,3000
,GETDATE()
,4000
,5000
,getdate()+15
,66666.6666
,777777
,88888.8888
,REPLICATE('C',8000)
,newid()
,getdate()+30
,CONVERT(VARBINARY(8000),REPLICATE('D',8000))
,REPLICATE('E',4000)
,1
,99999.9999
,10101.1111
,1100
,123456
,CONVERT(VARBINARY(MAX),REPLICATE('F',8000))
,REPLICATE('G',8000)
,0x4646464
,'H'
,REPLICATE('I',4000)
,'J'
,CONVERT(XML,REPLICATE('K',4000))
,REPLICATE('L',100)
)

GO
--Delete the data
Delete from Test_Table
Go
--Verify the data
Select * from Test_Table
Go
--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'
GO
--Recover the deleted data it with date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table','2012-06-01','2012-06-30'

Download Stored Procedure :

Now, you need to create the procedure to recover your deleted data

Watch on Youtube : How to recover the deleted Data in SQL Server

Explanation:

How does it work? Let’s go through it step by step. The process requires seven easy steps:

Step-1:

Context (‘LCX_MARK_AS_GHOST’and ‘LCX_HEAP’)
Operation (‘LOP_DELETE_ROWS’)
AllocUnitName(‘dbo.Student’) –- Schema + table Name

Here is the code snippet:

Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)WHERE AllocUnitName =‘dbo.Student’ AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)

This query will return number of columns providing different information, but we only need to select the column “RowLog content o, to get the deleted data.

The Column “RowLog content 0″ will look like this:

“0x300018000100000000000000006B0000564920205900000

00500E001002800426F62206A65727279″

Step-2:

1 Byte : Status Bit A
1 Byte : Status Bit B
2 Bytes : Fixed length size
n Bytes : Fixed length data
2 Bytes : Total Number of Columns
n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)
2 Bytes : Number of variable-length columns
n Bytes : Column offset array (2x variable length column)
n Bytes : Data for variable length columns

So, the Hex data“RowLog content 0″ is equal to:

Step-3:

Now, we need to break the RowLog Content o (Hex value of our deleted data) into the above defined structure.[Color codes are used for reference only]

[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
[Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
[Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)
[Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] )
Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )
Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)

Step-4:

Step-5:

Step-6:


--NVARCHAR ,NCHAR
WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))

--VARCHAR,CHAR
WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))

--TINY INTEGER
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value))))

--SMALL INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value))))

-- INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value))))

-- BIG INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))

--DATETIME
WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100)

--SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME

--- NUMERIC
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))

--MONEY,SMALLMONEY
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2)

--- DECIMAL
WHEN system_type_id = 106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), Convert(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))

-- BIT
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))

--- FLOAT
WHEN system_type_id = 62 THEN  RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value))))

--REAL
When  system_type_id =59 THEN  Left(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8)

--BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varbinary(max)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varchar(max)')

--UNIQUEIDENTIFIER
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))

Step-7:

Finally we do a pivot table over the data and you will see the result. THE DELETED DATA IS BACK.

Note: This data will only for display. It is not available in your selected table but you can insert this data in your table.

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Posted in Solutions, SQL SERVER | Tagged How to recover deleted data from SQL Server, How to recover deleted data from SQL Sever | 589 Comments »

« Newer Posts

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

How to recover deleted data from SQL Server 2000

How to use multiple values for IN clause using same parameter (SQL Server)

How to generate Insert statements from table data using SQL Server

How to recover deleted data from SQL Server

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer