How to recover deleted data from SQL Server

原文http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

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).

• 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

001	CREATE PROCEDURE Recover_Deleted_Data_Proc

002	@SchemaName_n_TableName NVARCHAR(Max),

003	@Date_From datetime='1900/01/01',

004	@Date_To datetime ='9999/12/31'

005

as

006

007	DECLARE @RowLogContents VARBINARY(8000)

008	DECLARE @TransactionID NVARCHAR(Max)

009	DECLARE @AllocUnitID BIGINT

010	Declare @AllocUnitName NVARCHAR(Max)

011	Declare @SQL NVARCHAR(Max)

012

013	DECLARE @bitTable TABLE

014

(

015

[ID] INT,

016	[Bitvalue] INT

017

)

018	--Create table to set the bit position of one byte.

019

020	INSERT INTO @bitTable

021	SELECT 0,2 UNION ALL

022	SELECT 1,2 UNION ALL

023	SELECT 2,4 UNION ALL

024	SELECT 3,8 UNION ALL

025	SELECT 4,16 UNION ALL

026	SELECT 5,32 UNION ALL

027	SELECT 6,64 UNION ALL

028	SELECT 7,128

029

030	--Create table to collect the row data.

031	DECLARE @DeletedRecords TABLE

032

(

033	[RowLogContents]    VARBINARY(8000),

034	[AllocUnitID]       BIGINT,

035	[Transaction ID]    NVARCHAR(Max),

036	[FixedLengthData]   SMALLINT,

037	[TotalNoOfCols]     SMALLINT,

038	[NullBitMapLength]  SMALLINT,

039	[NullBytes]         VARBINARY(8000),

040	[TotalNoofVarCols]  SMALLINT,

041	[ColumnOffsetArray] VARBINARY(8000),

042	[VarColumnStart]    SMALLINT,

043	[NullBitMap]        VARCHAR(MAX)

044

)

045	--Create a common table expression to get all the row data plus how many bytes we have for each row.

046	;WITH RowData AS (

047

SELECT

048

049	[RowLog Contents 0] AS [RowLogContents]

050

051	,[AllocUnitID] AS [AllocUnitID]

052

053	,[Transaction ID] AS [Transaction ID]

054

055	--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)

056	,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData

057

058	-- [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)

059	,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

060	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as  [TotalNoOfCols]

061

062	--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)

063	,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

064	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]

065

066	--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )

067	,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,

068	CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

069	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

070

071	--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )

072	,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN

073	CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

074	CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

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

076	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols]

077

078	--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )

079	,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN

080	SUBSTRING([RowLog Contents 0]

081	, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

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

083	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2

084	, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN

085	CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

086	CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

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

088	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)

089	* 2)  ELSE null  END) AS [ColumnOffsetArray]

090

091	--  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)

092	,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)

093

THEN  (

094	CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4

095

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

097	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))

098

099	+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN

100	CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

101	CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

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

103	,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2))

104

105	ELSE null End AS [VarColumnStart]

106

107	FROM    sys.fn_dblog(NULL, NULL)

108	WHERE   -- AllocUnitName ='' + @SchemaName_n_TableName + '' --'dbo.Student'

109	AllocUnitId IN

110	(Select [Allocation_unit_id] from sys.allocation_units allocunits

111	INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

112	AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

113	AND partitions.partition_id = allocunits.container_id)

114	Where object_id=object_ID('' + @SchemaName_n_TableName + ''))

115

116	AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')

117	And SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)

118

119	/*Use this subquery to filter the date*/

120	AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)

121	Where Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

122	And [Transaction Name]='DELETE'

123	And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

124

125	--Use this technique to repeate the row till the no of bytes of the row.

126	N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

127	N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

128	N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

129	N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

130	FROM N3 AS X, N3 AS Y)

131

132	insert into @DeletedRecords

133	Select  RowLogContents

134	,[AllocUnitID]

135	,[Transaction ID]

136	,[FixedLengthData]

137	,[TotalNoOfCols]

138	,[NullBitMapLength]

139	,[NullBytes]

140	,[TotalNoofVarCols]

141	,[ColumnOffsetArray]

142	,[VarColumnStart]

143	---Get the Null value against each column (1 means null zero means not null)

144	,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +

145	(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]

146

FROM

147	N4 AS Nums

148	Join RowData AS C ON n<=NullBitMapLength

149	Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))

150	FROM RowData D

151

152

153	CREATE TABLE [#temp_Data]

154

(

155	[FieldName]  VARCHAR(MAX),

156	[FieldValue] VARCHAR(MAX),

157	[Rowlogcontents] VARBINARY(8000)

158

159

)

160	--Create common table expression and join it with the rowdata table

161	-- to get each column details

162	;With CTE AS (

163	/*This part is for variable data columns*/

164	SELECT Rowlogcontents,

165

NAME ,

166	cols.leaf_null_bit AS nullbit,

167	leaf_offset,

168	ISNULL(syscolumns.length, cols.max_length) AS [length],

169	cols.system_type_id,

170	cols.leaf_bit_position AS bitpos,

171	ISNULL(syscolumns.xprec, cols.precision) AS xprec,

172	ISNULL(syscolumns.xscale, cols.scale) AS xscale,

173	SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

174	--Calculate the variable column size from the variable column offset array

175	(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN

176	CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],

177

178	---Calculate the column length

179	(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))))

180	- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

181	ELSE 0 END) AS [Column Length]

182

183	--Get the Hexa decimal value from the RowlogContent

184	--HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])

185	--This is the data of your column but in the Hexvalue

186	,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

187	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)

188	- ((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))))

189	- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

190	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))))

191	- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

192	ELSE 0 END))) END AS hex_Value

193

194	FROM @DeletedRecords A

195	Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

196	INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

197	AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

198	INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

199	LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

200	WHERE leaf_offset<0

201

202

UNION

203	/*This part is for fixed data columns*/

204	SELECT  Rowlogcontents,

205

NAME ,

206	cols.leaf_null_bit AS nullbit,

207	leaf_offset,

208	ISNULL(syscolumns.length, cols.max_length) AS [length],

209	cols.system_type_id,

210	cols.leaf_bit_position AS bitpos,

211	ISNULL(syscolumns.xprec, cols.precision) AS xprec,

212	ISNULL(syscolumns.xscale, cols.scale) AS xscale,

213	SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

214	(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM

215	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],

216	syscolumns.length AS [Column Length]

217

218	,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

219

SUBSTRING

220

(

221	Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM

222	sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5

223	,syscolumns.length) END AS hex_Value

224

225	FROM @DeletedRecords A

226	Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

227	INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

228	AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

229	INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

230	LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

231	WHERE leaf_offset>0 )

232

233	--Converting data from Hexvalue to its orgional datatype.

234	--Implemented datatype conversion mechanism for each datatype

235

236	INSERT INTO #temp_Data

237	SELECT NAME,

238

CASE

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

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

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

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

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

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

245	WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME

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

247	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

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

249	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

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

251

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

252

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

253	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

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

255

256	END AS FieldValue

257	,[Rowlogcontents]

258	FROM CTE ORDER BY nullbit

259

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

261

262	DECLARE @FieldName VARCHAR(max)

263	SET @FieldName = STUFF(

264

(

265	SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')

266

267	FOR XML PATH('')

268	), 1, 1, '')

269

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

271	SET @sql = 'SELECT ' + @FieldName  + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ')) AS pvt'

272

273	EXEC sp_executesql @sql

274

275

GO

276	--Execute the procedure like

277	--Recover_Deleted_Data_Proc 'Schema.table name'

278	--EXAMPLE #1 : FOR ALL DELETED RECORDS

279	EXEC Recover_Deleted_Data_Proc 'dbo.Student'

280

281	--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE

282	EXEC Recover_Deleted_Data_Proc 'dbo.Student' ,'2011/12/01','2012/01/30'

283

284	--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.

01	--NVARCHAR ,NCHAR

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

03

04	--VARCHAR,CHAR

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

06

07	--TINY INTEGER

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

09

10	--SMALL INTEGER

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

12

13	-- INTEGER

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

15

16	-- BIG INTEGER

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

18

19	--DATETIME

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

21

22	--SMALL DATETIME

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

24

25	--- NUMERIC

26	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))

27

28	--MONEY,SMALLMONEY

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

30

31	--- DECIMAL

32	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))

33

34

-- BIT

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

36

37

--- FLOAT

38

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))))

39

40

--REAL

41

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)

42

43	--BINARY,VARBINARY

44	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)')

45

46	--UNIQUEIDENTIFIER

47	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.