开发者社区> 杰克.陈> 正文

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 ...
+关注继续查看

原文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.

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
20650 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22329 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23573 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
13538 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
20895 0
+关注
杰克.陈
一个安静的程序猿~
10425
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载