简单的MSSQL恢复删除数据的方法
这段程序是在http://www.sqlservercentral.com中看到的，讲解了每一段语句都做了什么，很详细，我只是简单的将他的代码整理了一下。
代码如下：
1 Declare @Allocation_unit_id Nvarchar (20 )= ''; 2 Declare @Operation Nvarchar (20 )= 'LOP_DELETE_ROWS'; --LOP_INSERT_ROWS 插入数据 ,LOP_DELETE_ROWS 3 SELECT @Allocation_unit_id = allocunits. allocation_unit_id 4 FROM sys . allocation_units allocunits 5 INNER JOIN sys. partitions partitions ON ( allocunits .type IN ( 1, 3) 6 AND partitions .hobt_id = allocunits .container_id ) 7 OR ( allocunits. type = 2 and partitions . partition_id = allocunits . container_id) 8 INNER JOIN sysobjects objects ON partitions. object_id = objects. id 9 AND objects .type IN ( 'U', 'u') 10 WHERE partitions . index_id IN ( 0 , 1 ) and name like '%Crack_Me_If_You_Can%' 11 Print @Allocation_unit_id 12 13 14 15 DECLARE MyCursor CURSOR 16 FOR 17 -- Declare @Operation Nvarchar(20)='LOP_DELETE_ROWS'; Declare @Allocation_unit_id Nvarchar(20)='72057594041270272'; 18 SELECT 19 [Page ID], 20 [RowLog Contents 0] 21 FROM :: fn_dblog (NULL, NULL) dblog 22 INNER JOIN 23 ( 24 SELECT allocunits .allocation_unit_id , objects . name, objects. id 25 FROM sys .allocation_units allocunits 26 INNER JOIN sys . partitions partitions ON ( allocunits. type IN (1 , 3 ) 27 AND partitions .hobt_id = allocunits .container_id ) 28 OR ( allocunits. type = 2 and partitions . partition_id = allocunits . container_id) 29 INNER JOIN sysobjects objects ON partitions . object_id = objects . id 30 AND objects .type IN ( 'U', 'u') 31 WHERE partitions .index_id IN ( 0, 1) 32 ) allocunits ON dblog. AllocUnitID = allocunits. allocation_unit_id 33 INNER JOIN 34 ( 35 SELECT [Begin Time] ,[Transaction Name] , [Transaction ID] 36 FROM fn_dblog (NULL, NULL) x 37 WHERE Operation = @Operation 38 ) dblog1 ON dblog1. [Transaction ID] = dblog. [Transaction ID] 39 WHERE [Page ID] IS NOT NULL AND [Slot ID] >= 0 40 AND dblog .[Transaction ID] in 41 ( 42 SELECT [Transaction ID] FROM fn_dblog (NULL, NULL) WHERE AllocUnitId = @Allocation_unit_id 43 AND Operation =@Operation 44 ) 45 Print @Operation 46 47 OPEN MyCursor ; 48 -- 循环一个游标 49 50 DECLARE @RowLogContents VARBINARY (8000 ) =Null 51 DECLARE @Page_ID Nvarchar (Max ) =Null 52 FETCH NEXT FROM MyCursor INTO @Page_ID, @RowLogContents ; 53 WHILE @@FETCH_STATUS =0 54 BEGIN 55 /*-- Declare @Allocation_unit_id Nvarchar(20)='72057594039828480'; 56 DECLARE @RowLogContents VARBINARY(8000) =0x30001400010000006291BB0066A000002A000000050000020028003700446F6E27742050616E6963536861726520616E6420456E6A6F79 57 --*/ 58 59 SET @RowLogContents = @RowLogContents 60 61 DECLARE @lenFixedBytes SMALLINT=null, @noOfCols SMALLINT =null, @nullBitMapLength SMALLINT =null, @nullByteMap VARBINARY (MAX )=null,@nullBitMap VARCHAR( MAX )=null,@noVarCols SMALLINT =null, @columnOffsetArray VARBINARY ( MAX)=null, @varColPointer SMALLINT =null 62 63 64 SELECT 65 @lenFixedBytes = CONVERT( SMALLINT , CONVERT (BINARY ( 2), REVERSE (SUBSTRING ( @RowLogContents, 2 + 1 , 2 )))), 66 @noOfCols = CONVERT( INT , CONVERT (BINARY ( 2), REVERSE (SUBSTRING ( @RowLogContents, @lenFixedBytes + 1 , 2 )))), 67 @nullBitMapLength = CONVERT( INT , ceiling (@noOfCols / 8.0)), 68 @nullByteMap = SUBSTRING( @RowLogContents , @lenFixedBytes + 3 , @nullBitMapLength ), 69 @noVarCols = CASE WHEN SUBSTRING ( @RowLogContents, 1, 1) = 0x30 THEN 70 CONVERT (INT , CONVERT ( BINARY( 2 ), REVERSE (SUBSTRING ( @RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength , 2 )))) 71 ELSE null 72 END , 73 @columnOffsetArray = CASE WHEN SUBSTRING (@RowLogContents , 1 , 1 ) = 0x30 THEN 74 SUBSTRING (@RowLogContents , @lenFixedBytes + 3 + @nullBitMapLength + 2 , @noVarCols * 2) 75 ELSE null 76 END , 77 @varColPointer = CASE WHEN SUBSTRING ( @RowLogContents, 1 , 1 ) = 0x30 THEN 78 ( @lenFixedBytes + 2 + @nullBitMapLength + 2 + ( @noVarCols * 2 )) 79 ELSE null 80 END 81 82 DECLARE @byteTable TABLE 83 ( 84 byte INT 85 ) 86 87 DECLARE @cnt INT 88 SET @cnt = 1 89 WHILE (@cnt < @nullBitMapLength + 1) 90 BEGIN 91 INSERT INTO @byteTable( byte ) 92 VALUES (@cnt ) 93 SET @cnt = @cnt + 1 94 END 95 96 SELECT 97 @nullBitMap = COALESCE( @nullBitMap , '' ) + 98 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 128 ) % 2 ) + 99 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 64 ) % 2 ) + 100 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 32 ) % 2 ) + 101 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 16 ) % 2 ) + 102 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 8 ) % 2 ) + 103 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 4 ) % 2 ) + 104 CONVERT (NVARCHAR ( 1), (SUBSTRING ( @nullByteMap, byte, 1) / 2 ) % 2 ) + 105 CONVERT (NVARCHAR ( 1), SUBSTRING( @nullByteMap , byte , 1 ) % 2 ) 106 FROM @byteTable b 107 ORDER BY byte DESC 108 109 110 DECLARE @colOffsetTable TABLE 111 ( 112 colNum SMALLINT , 113 columnOffset VARBINARY ( 2), 114 columnOffvalue SMALLINT , 115 columnLength SMALLINT 116 ) 117 118 SET @cnt = 1 119 WHILE (@cnt <= @noVarCols ) 120 BEGIN 121 INSERT INTO @colOffsetTable( colNum , columnOffset , columnOffValue , columnLength ) 122 VALUES ( 123 @cnt * - 1 , 124 SUBSTRING ( @columnOffsetArray , ( 2 * @cnt ) - 1, 2), 125 CONVERT (SMALLINT , CONVERT ( BINARY( 2 ), REVERSE ( SUBSTRING ( @columnOffsetArray, (2 * @cnt ) - 1 , 2 )))), 126 CONVERT (SMALLINT , CONVERT ( BINARY( 2 ), REVERSE ( SUBSTRING ( @columnOffsetArray, (2 * @cnt ) - 1 , 2 )))) 127 - ISNULL ( NULLIF( CONVERT (SMALLINT , CONVERT ( BINARY( 2 ), REVERSE ( SUBSTRING ( @columnOffsetArray , ( 2 * ( @cnt - 1)) - 1 , 2 )))), 0 ), @varColPointer ) 128 ) 129 SET @cnt = @cnt + 1 130 END 131 132 ------------------- 133 134 135 DECLARE @schema TABLE 136 ( 137 [column] INT , 138 [length] INT , 139 [name] NVARCHAR ( max), 140 [system_type_id] INT , 141 [bitpos] INT , 142 [xprec] INT , 143 [xscale] INT , 144 [leaf_offset] INT , 145 [is_uniqueifier] BIT , 146 [is_null] BIT NULL 147 ) 148 149 INSERT INTO @schema 150 SELECT 151 cols .leaf_null_bit AS nullbit , 152 ISNULL (syscolumns . length, cols. max_length ) AS [length] , 153 CASE 154 WHEN is_uniqueifier = 1 THEN 'UNIQUIFIER' 155 ELSE isnull ( syscolumns. name , 'DROPPED' ) 156 END [name] , 157 cols .system_type_id , 158 cols .leaf_bit_position AS bitpos , 159 ISNULL (syscolumns . xprec, cols. precision ) AS xprec , 160 ISNULL (syscolumns . xscale, cols. scale ) AS xscale , 161 cols .leaf_offset , 162 is_uniqueifier , 163 SUBSTRING (REVERSE ( @nullBitMap), cols. leaf_null_bit , 1 ) AS is_null 164 FROM sys . allocation_units allocunits 165 INNER JOIN sys. partitions partitions ON ( allocunits .type IN ( 1, 3) 166 AND partitions . hobt_id = allocunits . container_id) OR ( allocunits. type = 2 AND partitions . partition_id = allocunits . container_id) 167 INNER JOIN sys. system_internals_partition_columns cols ON cols .partition_id = partitions .partition_id 168 LEFT OUTER JOIN syscolumns ON syscolumns. id = partitions .object_id 169 AND syscolumns . colid = cols . partition_column_id 170 WHERE allocunits . allocation_unit_id = @Allocation_unit_id 171 ORDER BY nullbit 172 173 INSERT INTO @schema 174 SELECT - 3 , 1 , 'StatusBitsA' , 0 , 0 , 0 , 0 , 2147483647 , 0 , 0 175 INSERT INTO @schema 176 SELECT - 2 , 1 , 'StatusBitsB' , 0 , 0 , 0 , 0 , 2147483647 , 0 , 0 177 INSERT INTO @schema 178 SELECT - 1 , 2 , 'LenFixedBytes' , 52 , 0 , 10 , 0 , 2147483647 , 0 , 0 179 180 SELECT distinct 181 [name] AS ColumnName, 182 CASE WHEN s. is_null = 1 THEN NULL ELSE 183 CASE 184 WHEN s . system_type_id IN ( 167 , 175 , 231 , 239 ) THEN LTRIM (RTRIM ( CONVERT( NVARCHAR (MAX ), REVERSE (REVERSE ( REPLACE( hex_string , 0x00 , 0x20 )))))) 185 WHEN s . system_type_id = 48 THEN CONVERT (NVARCHAR ( MAX), CONVERT (TINYINT , CONVERT ( BINARY( 1 ), REVERSE ( hex_string )))) 186 WHEN s . system_type_id = 52 THEN CONVERT (NVARCHAR ( MAX), CONVERT (SMALLINT , CONVERT ( BINARY( 2 ), REVERSE ( hex_string )))) 187 WHEN s . system_type_id = 56 THEN CONVERT (NVARCHAR ( MAX), CONVERT (INT , CONVERT ( BINARY( 4 ), REVERSE (hex_string )))) 188 WHEN s . system_type_id = 127 THEN CONVERT (NVARCHAR ( MAX), CONVERT( BIGINT , CONVERT (BINARY ( 8), REVERSE (hex_string )))) 189 WHEN s . system_type_id = 61 THEN 190 CONVERT (VARCHAR ( MAX), 191 CONVERT (DATETIME , SUBSTRING ( hex_string, 4, 1) + SUBSTRING (hex_string , 3 , 1 ) + SUBSTRING( hex_string , 2 , 1 ) + SUBSTRING (hex_string , 1 , 1 )) 192 + CONVERT ( DATETIME, DATEADD( dd , CONVERT (INT , SUBSTRING (hex_string , 8 , 1 ) + SUBSTRING( hex_string , 7 , 1 ) + SUBSTRING (hex_string , 6 , 1 ) +SUBSTRING( hex_string , 5 , 1 )), 0x00000000 )) 193 , 109 ) 194 WHEN s . system_type_id = 108 AND s .xprec = 5 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 5, 2 ), 0x050200 + hex_string )) 195 WHEN s . system_type_id = 108 AND s .xprec = 6 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 6, 2 ), 0x060200 + hex_string )) 196 WHEN s . system_type_id = 108 AND s .xprec = 6 AND s. xscale = 3 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 6, 3 ), 0x060300 + hex_string )) 197 WHEN s . system_type_id = 108 AND s .xprec = 7 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 7, 2 ), 0x070200 + hex_string )) 198 WHEN s . system_type_id = 108 AND s .xprec = 8 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 8, 2 ), 0x080200 + hex_string )) 199 WHEN s . system_type_id = 108 AND s .xprec = 9 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 9, 2 ), 0x090200 + hex_string )) 200 WHEN s . system_type_id = 108 AND s .xprec = 10 AND s. xscale = 2 THEN CONVERT ( VARCHAR( MAX ), CONVERT (NUMERIC ( 10, 2 ), 0x0A0200 + hex_string )) 201 END 202 END AS ClearText 203 FROM ( 204 SELECT 205 s .*, 206 CASE WHEN s. leaf_offset > 1 AND s . bitpos = 0 THEN 207 SUBSTRING 208 ( 209 @RowLogContents , 210 ISNULL ((SELECT TOP 1 SUM ( x. length ) FROM @schema x WHERE x. [column] < s. [column] AND x. leaf_offset > 1 AND x .bitpos = 0 ), 0 ) + 1 , 211 s .LENGTH 212 ) 213 ELSE 214 SUBSTRING 215 ( 216 @RowLogContents , 217 ( col. columnOffValue - col. columnLength ) + 1 , 218 col .columnLength 219 ) 220 END AS hex_string 221 FROM @schema s 222 LEFT OUTER JOIN @colOffsetTable col ON col .colNum = ( s. leaf_offset ) 223 ) AS s 224 WHERE [column] > 0 AND is_uniqueifier = 0 225 226 227 FETCH NEXT FROM MyCursor INTO @Page_ID, @RowLogContents ; 228 END 229 -- 关闭游标 230 CLOSE MyCursor 231 -- 释放资源 232 DEALLOCATE MyCursor
