恢复sqlserver被误删除的数据曾经想实现log explorer for sql server的功能，利用ldf里面的日志来还原误删除的数据
这里有一篇文章做到了，不过似乎不是所有的数据类型都支持
以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
在我使用sqlserver的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”
现在，从sqlserver2005 或以上版本能很容易能够恢复被删除的数据
（注意：这个脚本能恢复下面的数据类型的数据 而且兼容cs 排序规则）
imagetextuniqueidentifiertinyintsmallintintsmalldatetimerealmoneydatetimefloatsql_variantntextbitdecimalnumericsmallmoneybigintvarbinaryvarcharbinarychartimestampnvarcharncharxmlsysname
让我来用demo来解释一下我是怎么做到的
use mastergo--创建数据库create database testgouse [test]go--创建表create table [dbo].[aa]( [id] [int] identity(1,1) not null, [name] [nvarchar](200) null) on [primary]go--插入测试数据insert [dbo].[aa] ( [name] )select '你好'go--删除数据delete from aago--验证数据是否已经删除select * from aago
现在你需要创建一个存储过程来恢复你的数据
-- script name: recover_deleted_data_proc-- script type : recovery procedure -- develop by: muhammad imran-- date created: 15 oct 2011-- modify date: 22 aug 2012-- version : 3.1-- notes : included blob data types for recovery.& compatibile with default , cs collation , arabic_ci_as. create procedure recover_deleted_data_proc @database_name nvarchar(max) , @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 @compatibility_level int select @compatibility_level = dtb.compatibility_level from master.sys.databases as dtb where dtb.name = @database_name if isnull(@compatibility_level, 0) 80 begin raiserror('the compatibility level should be equal to or greater sql server 2005 (90)',16,1) return end if ( select count(*) from information_schema.tables where [table_schema] + '.' + [table_name] = @schemaname_n_tablename ) = 0 begin raiserror('could not found the table in the defined database',16,1) return end 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 ( [row id] int identity(1, 1) , [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 , [slot id] int , [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 ( 0x10, 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 ( 0x10, 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 ( 0x10, 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 ( 0x10, 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 ( 0x10, 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] , [slot id] from sys.fn_dblog(null, null) where allocunitid in ( select [allocation_unit_id] 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 ) where object_id = object_id('' + @schemaname_n_tablename + '') ) and context in ( 'lcx_mark_as_ghost', 'lcx_heap' ) and operation in ( 'lop_delete_rows' ) and substring([rowlog contents 0], 1, 1) in ( 0x10, 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] in ( 'delete', 'user_transaction' ) 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] , [slot id] ---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 if ( select count(*) from @deletedrecords ) = 0 begin raiserror('there is no data in the log as per the search criteria',16,1) return end declare @columnnameanddata table ( [row id] int , [rowlogcontents] varbinary(max) , [name] sysname , [nullbit] smallint , [leaf_offset] smallint , [length] smallint , [system_type_id] tinyint , [bitpos] tinyint , [xprec] tinyint , [xscale] tinyint , [is_null] int , [column value size] int , [column length] int , [hex_value] varbinary(max) , [slot id] int , [update] int ) --create common table expression and join it with the rowdata table-- to get each column details/*this part is for variable data columns*/--@rowlogcontents, --(col.columnoffvalue - col.columnlength) + 1,--col.columnlength--) insert into @columnnameanddata select [row id] , 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 , ( case when leaf_offset 1 and substring([nullbitmap], cols.leaf_null_bit, 1) = 0 then ( case when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 then convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - power(2, 15) else convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) end ) end ) as [column value size] , ( case when leaf_offset 1 and substring([nullbitmap], cols.leaf_null_bit, 1) = 0 then ( case when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) 30000 then ( case when [system_type_id] in ( 35, 34, 99 ) then 16 else 24 end ) when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) > 30000 then ( case when [system_type_id] in ( 35, 34, 99 ) then 16 else 24 end ) --24 when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) 30000 then ( convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) ) when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) > 30000 then power(2, 15) + convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) end ) end ) as [column length] , ( case when substring([nullbitmap], cols.leaf_null_bit, 1) = 1 then null else substring(rowlogcontents, ( ( case when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 then convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - power(2, 15) else convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) end ) - ( case when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) 30000 then ( case when [system_type_id] in ( 35, 34, 99 ) then 16 else 24 end ) --24 when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) > 30000 then ( case when [system_type_id] in ( 35, 34, 99 ) then 16 else 24 end ) --24 when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) 30000 then convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) when convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) 30000 and isnull(nullif(convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varcolumnstart]) > 30000 then power(2, 15) + convert(int, convert(binary(2), reverse(substring([columnoffsetarray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - isnull(nullif