恢复SQLSERVER被误删除的数据

USE master
GO
--创建数据库
CREATE DATABASE test
GO

USE [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 aa
Go



--验证数据是否已经删除
Select * from aa
Go

--恢复数据，不加时间段条件  参数：数据库名，表名
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
GO



--恢复数据，加时间段条件
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa','2014-04-23','2014-04-23'

EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
GO

SELECT  [RowLog Contents 0]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   AllocUnitName = 'dbo.aa'
        AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )
        AND Operation IN ( 'LOP_DELETE_ROWS' )

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

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

Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 525
Invalid length parameter passed to the LEFT or SUBSTRING function.

Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 651
XML parsing: line 1, character 2, illegal xml character

 

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

消息 50000，级别 16，状态 1，过程 Recover_Deleted_Data_Proc，第 290 行
There is no data in the log as per the search criteria