恢复SQLSERVER被误删除的数据
发布时间：2020-12-12 12:45:48 所属栏目：MsSql教程 来源：网络整理
导读：曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ? 在我使用SQLSER
|
曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ? 在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？” 现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据 （注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）
? ? 让我来用demo来解释一下我是怎么做到的 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
现在你需要创建一个存储过程来恢复你的数据 -- 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',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,CONVERT(INT,2)))) + 1,2)))) AS [TotalNoOfCols]
--[NullBitMapLength]=ceiling([Total No of Columns] /8.0),CEILING(CONVERT(INT,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],2)))) + 3,2)))) / 8.0))) AS [NullBytes]
--[TotalNoofVarCols] = Substring (RowLog content 0,[Null Bitmap length] + 2 ),( CASE WHEN SUBSTRING([RowLog Contents 0],1,1) IN (
0x10,0x30,0x70 )
THEN CONVERT(INT,2)))) + 3
+ CONVERT(INT,2)))) / 8.0)),2))))
ELSE NULL
END ) AS [TotalNoofVarCols]
--[ColumnOffsetArray]= Substring (RowLog content 0,[Null Bitmap length] + 2,[TotalNoofVarCols]*2 ),0x70 )
THEN SUBSTRING([RowLog Contents 0],2)))) + 3
+ CONVERT(INT,2)))) / 8.0))
+ 2,1) IN ( 0x10,0x70 )
THEN CONVERT(INT,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) IN (
0x10,0x70 )
THEN ( CONVERT(SMALLINT,2)))) + 4
+ CONVERT(INT,2)))) / 8.0))
+ ( ( CASE WHEN SUBSTRING([RowLog Contents 0],0x70 )
THEN CONVERT(INT,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],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,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('')
),''),','') )
FROM RowData D
IF ( SELECT COUNT(*)
FROM @DeletedRecords
) = 0
BEGIN
RAISERROR('There is no data in the log as per the search criteria',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),[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],1) = 0
THEN ( CASE WHEN CONVERT(INT,REVERSE(SUBSTRING([ColumnOffsetArray],( 2
* leaf_offset
* -1 ) - 1,2)))) > 30000
THEN CONVERT(INT,2))))
- POWER(2,15)
ELSE CONVERT(INT,2))))
END )
END ) AS [Column value Size],2)))) > 30000
AND ISNULL(NULLIF(CONVERT(INT,( 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,[varColumnStart]) > 30000
THEN ( CASE WHEN [System_type_id] IN (
35,99 ) THEN 16
ELSE 24
END ) --24
WHEN CONVERT(INT,2)))) < 30000
AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
THEN ( CONVERT(INT,2))))
- ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) )
WHEN CONVERT(INT,[varColumnStart]) > 30000
THEN POWER(2,15)
+ CONVERT(INT,2))))
- ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
END )
END ) AS [Column Length],( CASE WHEN SUBSTRING([nullBitMap],1) = 1
THEN NULL
ELSE SUBSTRING(Rowlogcontents,( ( CASE WHEN CONVERT(INT,2)))) > 30000
THEN CONVERT(INT,2))))
- POWER(2,15)
ELSE CONVERT(INT,2))))
END )
- ( CASE WHEN CONVERT(INT,2)))) > 30000
AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
THEN ( CASE
WHEN [System_type_id] IN (
35,99 )
THEN 16
ELSE 24
END ) --24
WHEN CONVERT(INT,[varColumnStart]) > 30000
THEN ( CASE
WHEN [System_type_id] IN (
35,2)))) < 30000
AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
THEN CONVERT(INT,2))))
- ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
WHEN CONVERT(INT,[varColumnStart]) > 30000
THEN POWER(2,15)
+ CONVERT(INT,[varColumnStart])
END ) ) + 1,( CASE WHEN CONVERT(INT,2)))) > 30000
AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
THEN ( CASE WHEN [System_type_id] IN (
35,99 )
THEN 16
ELSE 24
END ) --24
WHEN CONVERT(INT,[varColumnStart]) > 30000
THEN ( CASE WHEN [System_type_id] IN (
35,2)))) < 30000
AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
THEN ABS(CONVERT(INT,[varColumnStart]))
WHEN CONVERT(INT,[varColumnStart]) > 30000
THEN POWER(2,15)
+ CONVERT(INT,2))))
- ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
END ))
END ) AS hex_Value,[Slot ID],0
FROM @DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
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
WHERE leaf_offset < 0
UNION
/*This part is for fixed data columns*/
SELECT [Row ID],( SELECT TOP 1
ISNULL(SUM(CASE WHEN C.leaf_offset > 1
THEN max_length
ELSE 0
END),0)
FROM 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],syscolumns.length AS [Column Length],CASE WHEN SUBSTRING([nullBitMap],1) = 1
THEN NULL
ELSE SUBSTRING(Rowlogcontents,( SELECT TOP 1
ISNULL(SUM(CASE
WHEN C.leaf_offset > 1
AND C.leaf_bit_position = 0
THEN max_length
ELSE 0
END),0)
FROM sys.system_internals_partition_columns C
WHERE cols.partition_id = C.partition_id
AND C.leaf_null_bit < cols.leaf_null_bit
) + 5,syscolumns.length)
END AS hex_Value,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
WHERE leaf_offset > 0
ORDER BY nullbit
DECLARE @BitColumnByte AS INT
SELECT @BitColumnByte = CONVERT(INT,CEILING(COUNT(*) / 8.0))
FROM @ColumnNameAndData
WHERE [System_Type_id] = 104;
WITH N1 ( n )
AS ( SELECT 1
UNION ALL
SELECT 1
),N3 AS Y
),CTE
AS ( SELECT RowLogContents,[nullbit],[BitMap] = CONVERT(VARBINARY(1),SUBSTRING(( REPLACE(STUFF(( SELECT
',( SUBSTRING(hex_Value,( ( SUBSTRING(hex_Value,1)
/ [Bitvalue] )
% 2 ))
END ) --as [nullBitMap]
FROM
N4 AS Nums
JOIN @ColumnNameAndData
AS C ON n <= @BitColumnByte
AND [System_Type_id] = 104
AND bitpos = 0
CROSS JOIN @bitTable
WHERE
C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents],n ASC
FOR
XML
PATH('')
),'') ),bitpos + 1,1)))
FROM @ColumnNameAndData D
WHERE [System_Type_id] = 104
)
UPDATE A
SET [hex_Value] = [BitMap]
FROM @ColumnNameAndData A
INNER JOIN CTE B ON A.[RowLogContents] = B.[RowLogContents]
AND A.[nullbit] = B.[nullbit]
/**************Check for BLOB DATA TYPES******************************/
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @CurrentLSN INT
DECLARE @LinkID INT
DECLARE @Context VARCHAR(50)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @LCX_TEXT_MIX VARBINARY(MAX)
DECLARE @temppagedata TABLE
(
[ParentObject] SYSNAME,[Object] SYSNAME,[Field] SYSNAME,[Value] SYSNAME
)
DECLARE @pagedata TABLE
(
[Page ID] SYSNAME,[File IDS] INT,[Page IDS] INT,[AllocUnitId] BIGINT,[ParentObject] SYSNAME,[Value] SYSNAME
)
DECLARE @ModifiedRawData TABLE
(
[ID] INT IDENTITY(1,[PAGE ID] VARCHAR(MAX),[FILE IDS] INT,[PAGE IDS] INT,[RowLog Contents 0_var] VARCHAR(MAX),[RowLog Length] VARCHAR(50),[RowLog Len] INT,[RowLog Contents 0] VARBINARY(MAX),[Link ID] INT DEFAULT ( 0 ),[Update] INT
)
DECLARE Page_Data_Cursor CURSOR
FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No,Page ID & AllocUnit ID*/
SELECT LTRIM(RTRIM(REPLACE([Description],'Deallocated',''))) AS [PAGE ID],[AllocUnitId],NULL AS [RowLog Contents 0],Context
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 Operation IN ( 'LOP_MODIFY_ROW' )
AND [Context] IN ( 'LCX_PFS' )
AND Description LIKE '%Deallocated%'
/*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] = 'DELETE'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From
AND
@Date_To )
GROUP BY [Description],Context
UNION
SELECT [PAGE ID],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
FROM sys.fn_dblog(NULL,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_TEXT_MIX' )
AND Operation IN ( 'LOP_DELETE_ROWS' )
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (
SELECT DISTINCT
[TRANSACTION ID]
FROM sys.fn_dblog(NULL,[Begin Time]) BETWEEN @Date_From
AND
@Date_To )
/****************************************/
OPEN Page_Data_Cursor
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID,@Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @hex_pageid AS VARCHAR(MAX)
/*Page ID contains File Number and page number It looks like 0001:00000130.
In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/
SET @Fileid = SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,@ConsolidatedPageID)
+ 1,LEN(@ConsolidatedPageID)) ---Seperate the page ID
SELECT @Pageid = CONVERT(INT,CAST('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )','varbinary(max)')) -- Convert Page ID from hex to integer
FROM ( SELECT CASE SUBSTRING(@hex_pageid,2)
WHEN '0x' THEN 3
ELSE 0
END
) AS t ( pos )
IF @Context = 'LCX_PFS'
BEGIN
DELETE @temppagedata
INSERT INTO @temppagedata
EXEC
( 'DBCC PAGE(' + @DataBase_Name + ','
+ @fileid + ',' + @pageid
+ ',1) with tableresults,no_infomsgs;'
);
INSERT INTO @pagedata
SELECT @ConsolidatedPageID,@fileid,@pageid,[ParentObject],[Object],[Field],[Value]
FROM @temppagedata
END
ELSE
IF @Context = 'LCX_TEXT_MIX'
BEGIN
INSERT INTO @ModifiedRawData
SELECT @ConsolidatedPageID,NULL,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,0
END
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID,@Context
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
--The data is in multiple rows in the page,so we need to convert it into one row as a single hex value.
--This hex value is in string format
INSERT INTO @ModifiedRawData
( [PAGE ID],[FILE IDS],[PAGE IDS],[RowLog Contents 0_var],[RowLog Length]
)
SELECT [Page ID],SUBSTRING([ParentObject],CHARINDEX('Slot',[ParentObject]) + 4,( CHARINDEX('Offset',[ParentObject])
- ( CHARINDEX('Slot',[ParentObject]) + 4 ) )
- 2) AS [Slot ID],SUBSTRING(( SELECT REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],[Value]) + 1,CHARINDEX('?',[Value])
- CHARINDEX(':',[Value])),'?','')
FROM @pagedata C
WHERE B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],B.[ParentObject])
+ 4,B.[ParentObject])
- ( CHARINDEX('Slot',B.[ParentObject])
+ 4 ) )) = SUBSTRING(C.[ParentObject],C.[ParentObject])
+ 4,C.[ParentObject])
- ( CHARINDEX('Slot',C.[ParentObject])
+ 4 ) ))
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],[Value]) - 1)
FOR
XML PATH('')
),' ','')
),20000) AS [Value],SUBSTRING(( SELECT '0x'
+ REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],'')
FROM
@pagedata C
WHERE
B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],C.[ParentObject])
+ 4 ) ))
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],[Value]) - 1)
FOR
XML PATH('')
),4) AS [Length]
FROM @pagedata B
WHERE [Object] LIKE '%Memory Dump%'
GROUP BY [Page ID],[AllocUnitId]--,[Current LSN]
ORDER BY [Slot ID]
UPDATE @ModifiedRawData
SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))','varbinary(Max)')))
FROM @ModifiedRawData
WHERE [LINK ID] = 0
UPDATE @ModifiedRawData
SET [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),'varbinary(Max)')
FROM @ModifiedRawData
WHERE [LINK ID] = 0
UPDATE B
SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN A.[RowLog Contents 0]
+ C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NULL
THEN A.[RowLog Contents 0]
END ),B.[Update] = ISNULL(B.[Update],0) + 1
FROM @ModifiedRawData B
LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT,CONVERT(VARBINARY(MAX),REVERSE(SUBSTRING(B.[RowLog Contents 0],15 + 14,2))))
AND A.[File IDS] = CONVERT(INT,19 + 14,2))))
AND A.[Link ID] = B.[Link ID]
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT,27 + 14,2))))
AND C.[File IDS] = CONVERT(INT,31 + 14,2))))
AND C.[Link ID] = B.[Link ID]
WHERE ( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
)
UPDATE B
SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN A.[RowLog Contents 0]
+ C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NULL
THEN A.[RowLog Contents 0]
END )
--,B.[Update]=ISNULL(B.[Update],0)+1
FROM @ModifiedRawData B
LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT,2))))
AND A.[Link ID] <> B.[Link ID]
AND B.[Update] = 0
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT,2))))
AND C.[Link ID] <> B.[Link ID]
AND B.[Update] = 0
WHERE ( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
)
UPDATE @ModifiedRawData
SET [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 8000
THEN SUBSTRING([RowLog Contents 0],[RowLog Len])
WHEN [RowLog Len] < 8000
THEN SUBSTRING([RowLog Contents 0],15 + 6,6)))))
END )
FROM @ModifiedRawData
WHERE [LINK ID] = 0
UPDATE @ColumnNameAndData
SET [hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT,REVERSE(SUBSTRING([hex_value],17,4)))) = [PAGE IDS]
AND CONVERT(INT,SUBSTRING([hex_value],9,2)) = B.[Link ID]
WHERE [System_Type_Id] IN ( 99,167,175,231,239,241,165,98 )
AND [Link ID] <> 0
UPDATE @ColumnNameAndData
SET [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN B.[RowLog Contents 0]
+ C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NULL
AND C.[RowLog Contents 0] IS NOT NULL
THEN C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NOT NULL
AND C.[RowLog Contents 0] IS NULL
THEN B.[RowLog Contents 0]
END )
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON CONVERT(INT,5,4)))) = B.[PAGE IDS]
AND B.[Link ID] = 0
LEFT JOIN @ModifiedRawData C ON CONVERT(INT,4)))) = C.[PAGE IDS]
AND C.[Link ID] = 0
WHERE [System_Type_Id] IN ( 99,98 )
AND ( B.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
)
UPDATE @ColumnNameAndData
SET [hex_Value] = [RowLog Contents 0]
--,3,2)) = [Link ID]
WHERE [System_Type_Id] IN ( 35,99 )
AND [Link ID] <> 0
UPDATE @ColumnNameAndData
SET [hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+10
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT,4)))) = [PAGE IDS]
WHERE [System_Type_Id] IN ( 35,99 )
AND [Link ID] = 0
UPDATE @ColumnNameAndData
SET [hex_Value] = [RowLog Contents 0]
--,99 )
AND [Link ID] = 0
UPDATE @ColumnNameAndData
SET [hex_value] = 0xFFFE + SUBSTRING([hex_value],LEN([hex_value]))
--,[Update]=[Update]+1
WHERE [system_type_id] = 241
CREATE TABLE [#temp_Data]
(
[FieldName] VARCHAR(MAX),[FieldValue] NVARCHAR(MAX),[Rowlogcontents] VARBINARY(8000),[Row ID] INT
)
INSERT INTO #temp_Data
SELECT NAME,CASE WHEN system_type_id IN ( 231,239 )
THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),hex_Value))) --NVARCHAR,NCHAR
WHEN system_type_id IN ( 167,175 )
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),hex_Value))) --VARCHAR,CHAR
WHEN system_type_id IN ( 35 )
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),hex_Value))) --Text
WHEN system_type_id IN ( 99 )
THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),hex_Value))) --nText
WHEN system_type_id = 48
THEN CONVERT(VARCHAR(MAX),CONVERT(TINYINT,CONVERT(BINARY(1),REVERSE(hex_Value)))) --TINY INTEGER
WHEN system_type_id = 52
THEN CONVERT(VARCHAR(MAX),REVERSE(hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56
THEN CONVERT(VARCHAR(MAX),CONVERT(BINARY(4),REVERSE(hex_Value)))) -- INTEGER
WHEN system_type_id = 127
THEN CONVERT(VARCHAR(MAX),CONVERT(BIGINT,CONVERT(BINARY(8),REVERSE(hex_Value))))-- BIG INTEGER
WHEN system_type_id = 61
THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --DATETIME
WHEN system_type_id = 58
THEN CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,100) --SMALL DATETIME
WHEN system_type_id = 108
THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20),CONVERT(VARBINARY(1),xprec)
+ CONVERT(VARBINARY(1),xscale))
+ CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC
WHEN system_type_id = 106
THEN CONVERT(VARCHAR(MAX),CONVERT(DECIMAL(38,0) + hex_Value)) --- DECIMAL
WHEN system_type_id IN ( 60,122 )
THEN CONVERT(VARCHAR(MAX),CONVERT(MONEY,2) --MONEY,SMALLMONEY
WHEN system_type_id = 104
THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,hex_Value)
% 2)) -- BIT
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
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
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
WHEN system_type_id = 34
THEN ( CASE WHEN CHARINDEX(0x,'varchar(max)') --IMAGE
WHEN system_type_id = 36
THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
WHEN system_type_id = 231
THEN CONVERT(VARCHAR(MAX),CONVERT(SYSNAME,hex_Value)) --SYSNAME
WHEN system_type_id = 241
THEN CONVERT(VARCHAR(MAX),CONVERT(XML,hex_Value)) --XML
WHEN system_type_id = 189
THEN ( CASE WHEN CHARINDEX(0x,'varchar(max)') --TIMESTAMP
WHEN system_type_id = 98
THEN ( CASE WHEN CONVERT(INT,SUBSTRING(hex_Value,1)) = 56
THEN CONVERT(VARCHAR(MAX),REVERSE(SUBSTRING(hex_Value,LEN(hex_Value)))))) -- INTEGER
WHEN CONVERT(INT,1)) = 108
THEN CONVERT(VARCHAR(MAX),1))
+ CONVERT(VARBINARY(1),4,0)
+ SUBSTRING(hex_Value,LEN(hex_Value)))) --- NUMERIC
WHEN CONVERT(INT,1)) = 167
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),LEN(hex_Value))))) --VARCHAR,CHAR
WHEN CONVERT(INT,1)) = 36
THEN CONVERT(VARCHAR(MAX),SUBSTRING(( hex_Value ),20))) --UNIQUEIDENTIFIER
WHEN CONVERT(INT,1)) = 61
THEN CONVERT(VARCHAR(MAX),LEN(hex_Value))))),100) --DATETIME
WHEN CONVERT(INT,1)) = 165
THEN '0x'
+ SUBSTRING(( CASE WHEN CHARINDEX(0x,'VARBINARY(8000)')) = 0
THEN '0x'
ELSE ''
END )
+ CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)'),LEN(hex_Value)) -- BINARY,VARBINARY
END )
END AS FieldValue,[Rowlogcontents],[Row ID]
FROM @ColumnNameAndData
ORDER BY nullbit
--Create the column name in the same order to do pivot table.
DECLARE @FieldName VARCHAR(MAX)
SET @FieldName = STUFF(( SELECT ','
+ CAST(QUOTENAME([Name]) AS VARCHAR(MAX))
FROM syscolumns
WHERE id = OBJECT_ID(''
+ @SchemaName_n_TableName
+ '')
FOR
XML PATH('')
),'')
--Finally did pivot table and get the data back in the same format.
SET @sql = 'SELECT ' + @FieldName
+ ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN ('
+ @FieldName + ')) AS pvt'
EXEC sp_executesql @sql
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 解释 究竟他是如何工作的?让我们来一步一步来,这个过程涉及到7个步骤: 步骤1: 我们需要获得SQLSERVER删除的数据记录.使用标准SQLSERVER函数fn_dblog，我们能够容易的获得事务日志记录（包括 已删的数据。不过，我们只需要事务日志中选中的被删数据，所以我们的过滤条件需要包含3个字段 Context,Operation &?AllocUnitName） ? 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可以说明是堆表还是聚集表 Operation：删除操作 AllocUnitName：分配单元名称，表名 下面是一个代码片段 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' )
这个查询会返回不同列的信息，但是我们只需要选择[RowLog Contents 0]列，去获得被删除的数据的内容 RowLog?content 0列的内容类似于这样 “0x300018000100000000000000006B0000564920205900000 00500E001002800426F62206A65727279″ 剩余步骤请参照一下链接： http://www.cnblogs.com/lyhabc/p/3683147.html 公司DBA说这个方法是几年前的老方法了，如果删除数据量大的话可能恢复不了或者只能回复部分数据。 恢复数据最好的方法还是定期备份，根据备份还原。 上面说的方法我也试过了，按照他的步骤确实可以进行恢复。但如果时删除3000+数据的话，跑了2个多小时，最后报错退出，一条都没有恢复成功！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |