求助别人带做生意什，SQL数据被别人删除了，怎么找回来
点击联系发帖人
时间：2016-11-18 07:20
sql数据库delete删除后怎么恢复，这是网上找的答案。。希望大神验证指教一下 - 洛晨随风 - 推酷
sql数据库delete删除后怎么恢复，这是网上找的答案。。希望大神验证指教一下 - 洛晨随风
使用Log Explorer查看和恢复数据
Log Explorer 4.1.可用于SQL Server2005的日志查看工具
http://download.csdn.net/source/620271
打开Log Explorer -& Attach Log File -& 选择SQL Server服务器和登陆方式 -& Connect -&
在Database Name中选择数据库 -& Attach-& 左面对话框中Browse-& View Log-& 就可以看到log记录了
想恢复的话: 右键Log记录 Undo Transation-& 选择保存文件名和路径-& 然后打开该文件到查询分析器里执行
T-sql代码就可以了
例如 如果Log是delete table where ...的话,生成的文件代码就是insert table ....
然后将此insert table的代码放到查询分析器里执行.就可以恢复数据.
----------------------------------------------------------------------
--如何恢复被delete/update的数据
----------------------------------------------------------------------
1 连接到被删除数据库的Db
打开log explorer
&file&-&&attach log file&-&选择服务器和登陆方式-&&connect&-&选择&数据库&-&&attach&
在左面操作项目的对话框中选择&browse&项目-&&view log&-&就可以看到当前的Log记录了
右键某一条log记录,选择&undo transation&-&&选择保存文件名和路径&-&然后打开该文件到查询分析器里执行
T-sql代码就可以了
例如: 如果log是delete table where ...的话,生成的文件代码就是insert table ....
----------------------------------------------------------------------
--Log Explorer恢复被drop table和truncate table后的数据
----------------------------------------------------------------------
连接到被删除数据库的Db
选择&salvaage dropped/truncate&菜单,在右边的对话框中选择表名,和droped/trucated的日期,
File Name中选择生成insert语句脚步的存放位置,condition选择是droped还是truncated,
最后点击&create& 就会生成insert语句,把生成的语句到查询分析器里面执行一下就可以了
2) 选择&ViewDDL Commands&菜单-&选&truncate table& 操作项-&点击&Salvage&-&生成语句-&查询分析器里执行
----------------------------------------------------------------------
--log explorer使用的几个问题
----------------------------------------------------------------------
1) 对数据库做完全/差异/日志备份
备份时如果选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时,提示No log recorders found that match the filter，would you like to view unfiltered data 选择yes 就看不到刚才的记录了
如果不选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时，就能看到原来的日志并做恢复
修改了其中一个表中的部分数据，此时用Log explorer看日志，可以作日志恢复
然后恢复备份，(注意:恢复是断开log explorer与数据库的连接，或连接到其他数据上，
否则会出现数据库正在使用无法恢复)
恢复完后，再打开log explorer 提示No log recorders found that match the filter，would you like to view unfiltered data,选择yes 就看不到刚才在2中修改的日志记录，所以无法做恢复.
不要用SQL的备份功能备份,搞不好你的日志就破坏了.
正确的备份方法是:
停止SQL服务,复制数据文件及日志文件进行文件备份.
然后启动SQL服务,用log explorer恢复数据
已发表评论数()
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
主题不准确
没有分页内容
图片无法显示
视频无法显示
与原文不一致oracle中及时找回被删除的数据
oracle中及时找回被删除的数据
select * from whir$t3068 as of timestamp to_timestamp( ' 9:00:00' , 'yyyy-mm-dd hh24:mi:ss' )&
select * from whir$t3068 as of timestamp sysdate-750/1440;&
flashBack table scheme.whir$t3068 to timestamp(timeVariable,' 09:mi:ss')&
********************这个效果很好***************************&
先执行SQL查询出来被删除的数据&
select * from 表名 as of timestamp sysdate-1/12&
在把数据添加到删除的表中&
insert into biao select &* from 表名 as of timestamp sysdate-1/12&Sql Server2005数据库误删除表恢复方法
您现在位置： &&
Sql Server2005数据库误删除表恢复方法
假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE Db.dbo.TB_test
--保存删除表的时间
SELECT dt=GETDATE() INTO #
--在删除操作后,发现不应该删除表 Db.dbo.TB_test
--下面演示了如何恢复这个误删除的表 Db.dbo.TB_test
--首先,备份事务日志(使用事务日志才能还原到指定的时间点)
BACKUP LOG Db TO DISK='c:\db_log.bak' WITH FORMAT
--接下来,我们要先还原完全备份(还原日志必须在还原完全备份的基础上进行)
RESTORE DATABASE Db FROM DISK='c:\db.bak' WITH REPLACE,NORECOVERY
--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM #
--获取比表被删除的时间略早的时间
RESTORE LOG Db FROM DISK='c:\db_log.bak' WITH RECOVERY,STOPAT=@dt
--查询一下,看表是否恢复
SELECT * FROM Db.dbo.TB_test
-----------
（所影响的行数为 0 行）
--测试成功
飞客数据恢复中心
飞客咨询电话 400-779-66963883人阅读
数据库（3）
曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据
这里有一篇文章做到了，不过似乎不是所有的数据类型都支持
以下为译文：
在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”
现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据
（注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）
imagetextuniqueidentifiertinyintsmallintintsmalldatetimerealmoneydatetimefloatsql_variantntextbitdecimalnumericsmallmoneybigintvarbinaryvarcharbinarychartimestampnvarcharncharxmlsysname
让我来用demo来解释一下我是怎么做到的
USE master
--创建数据库
CREATE DATABASE test
USE [test]
CREATE TABLE [dbo].[aa](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](200) NULL
) ON [PRIMARY]
--插入测试数据
INSERT [dbo].[aa]
( [NAME] )
SELECT '你好'
--删除数据
Delete from aa
--验证数据是否已经删除
Select * from aa
现在你需要创建一个存储过程来恢复你的数据
-- 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
-- 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 = '' ,
@Date_To DATETIME = ''
DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Compatibility_Level INT
@Compatibility_Level = patibility_level
master.sys.databases AS dtb
dtb.name = @Database_Name
IF ISNULL(@Compatibility_Level, 0) &= 80
RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
IF ( SELECT COUNT(*)
INFORMATION_SCHEMA.TABLES
[TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName
RAISERROR('Could not found the table in the defined database',16,1)
DECLARE @bitTable TABLE
[ID] INT ,
[Bitvalue] INT
--Create table to set the bit position of one byte.
INTO @bitTable
--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.
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))))
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))
( 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))))
END ) * 2)
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))))
END ) * 2 ) )
END AS [VarColumnStart] ,
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
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
@Date_To )
--Use this technique to repeate the row till the no of bytes of the row.
AS ( SELECT
AS ( SELECT
AS ( SELECT
AS ( SELECT
ROW_NUMBER() OVER ( ORDER BY X.n )
INTO @DeletedRecords
RowLogContents ,
[AllocUnitID] ,
[Transaction ID] ,
[FixedLengthData] ,
[TotalNoOfCols] ,
[NullBitMapLength] ,
[NullBytes] ,
[TotalNoofVarCols] ,
[ColumnOffsetArray] ,
[VarColumnStart] ,
---Get the Null value against each column (1 means null zero means not null)
[NullBitMap] = ( REPLACE(STUFF(( SELECT
','
WHEN [ID] = 0
THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes,
n, 1) % 2 ))
ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes,
/ [Bitvalue] )
END ) --as [nullBitMap]
FROM N4 AS Nums
JOIN RowData AS C ON n &= NullBitMapLength
CROSS JOIN @bitTable
C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents] ,
XML PATH('')
), 1, 1, ''), ',', '') )
IF ( SELECT COUNT(*)
@DeletedRecords
RAISERROR('There is no data in the log as per the search criteria',16,1)
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
INTO @ColumnNameAndData
[Row ID] ,
Rowlogcontents ,
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,
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- POWER(2, 15)
ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
END ) AS [Column value Size] ,
( CASE WHEN leaf_offset & 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit,
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 ) THEN 16
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 ) THEN 16
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) )
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) AS [Column Length] ,
( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1
ELSE SUBSTRING(Rowlogcontents,
( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- POWER(2, 15)
ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE
WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE
WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) ) + 1,
( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]))
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) AS hex_Value ,
[Slot ID] ,
@DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
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
leaf_offset & 0
/*This part is for fixed data columns*/
[Row ID] ,
Rowlogcontents ,
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 ,
( SELECT TOP 1
ISNULL(SUM(CASE WHEN C.leaf_offset & 1
THEN max_length
sys.system_internals_partition_columns C
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], cols.leaf_null_bit, 1) = 1
ELSE SUBSTRING(Rowlogcontents,
( SELECT TOP 1
ISNULL(SUM(CASE
WHEN C.leaf_offset & 1
AND C.leaf_bit_position = 0
THEN max_length
sys.system_internals_partition_columns C
cols.partition_id = C.partition_id
AND C.leaf_null_bit & cols.leaf_null_bit
) + 5, syscolumns.length)
END AS hex_Value ,
[Slot ID] ,
@DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
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
leaf_offset & 0
ORDER BY nullbit
DECLARE @BitColumnByte AS INT
@BitColumnByte = CONVERT(INT, CEILING(COUNT(*) / 8.0))
@ColumnNameAndData
[System_Type_id] = 104;
AS ( SELECT
AS ( SELECT
AS ( SELECT
AS ( SELECT
ROW_NUMBER() OVER ( ORDER BY X.n )
AS ( SELECT
RowLogContents ,
[nullbit] ,
[BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT
','
WHEN [ID] = 0
THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value,
n, 1) % 2 ))
ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value,
/ [Bitvalue] )
END ) --as [nullBitMap]
N4 AS Nums
JOIN @ColumnNameAndData
AS C ON n &= @BitColumnByte
AND [System_Type_id] = 104
AND bitpos = 0
CROSS JOIN @bitTable
C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents] ,
PATH('')
), 1, 1, ''),
',', '') ),
bitpos + 1, 1)))
@ColumnNameAndData D
[System_Type_id] = 104
[hex_Value] = [BitMap]
@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 ,
[Object] SYSNAME ,
[Field] SYSNAME ,
[Value] SYSNAME
DECLARE @ModifiedRawData TABLE
[ID] INT IDENTITY(1, 1) ,
[PAGE ID] VARCHAR(MAX) ,
[FILE IDS] INT ,
[PAGE IDS] INT ,
[Slot ID] INT ,
[AllocUnitId] BIGINT ,
[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
/*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*/
LTRIM(RTRIM(REPLACE([Description], 'Deallocated', ''))) AS [PAGE ID] ,
[Slot ID] ,
[AllocUnitId] ,
NULL AS [RowLog Contents 0] ,
NULL AS [RowLog Contents 0] ,
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
Context IN ( 'LCX_NULL' )
AND Operation IN ( 'LOP_BEGIN_XACT' )
AND [Transaction Name] = 'DELETE'
AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
@Date_To )
GROUP BY [Description] ,
[Slot ID] ,
[AllocUnitId] ,
[PAGE ID] ,
[Slot ID] ,
[AllocUnitId] ,
SUBSTRING([RowLog Contents 0], 15,
LEN([RowLog Contents 0])) AS [RowLog Contents 0] ,
CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) ,
Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
Context IN ( 'LCX_NULL' )
AND Operation IN ( 'LOP_BEGIN_XACT' )
AND [Transaction Name] = 'DELETE'
AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
@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
DECLARE @hex_pageid AS VARCHAR(MAX)
/*Page ID contains File Number and page number It looks like 0.
In this example 0001 is file Number &
is Page Number & These numbers are in Hex format*/
SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0,
CHARINDEX(':', @ConsolidatedPageID)) -- Seperate File ID from Page ID
SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,
CHARINDEX(':',
@ConsolidatedPageID)
+ 1, LEN(@ConsolidatedPageID))
---Seperate the page ID
@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
CASE SUBSTRING(@hex_pageid, 1, 2)
WHEN '0x' THEN 3
) AS t ( pos )
IF @Context = 'LCX_PFS'
@temppagedata
INTO @temppagedata
( 'DBCC PAGE(' + @DataBase_Name + ', '
+ @fileid + ', ' + @pageid
+ ', 1) with tableresults,no_'
INTO @pagedata
@ConsolidatedPageID ,
@AllocUnitID ,
[ParentObject] ,
[Object] ,
@temppagedata
IF @Context = 'LCX_TEXT_MIX'
INTO @ModifiedRawData
@ConsolidatedPageID ,
@AllocUnitID ,
CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,
11, 2)))) ,
@LCX_TEXT_MIX ,
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
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
INTO @ModifiedRawData
( [PAGE ID] ,
[FILE IDS] ,
[PAGE IDS] ,
[Slot ID] ,
[AllocUnitId] ,
[RowLog Contents 0_var] ,
[RowLog Length]
[Page ID] ,
[FILE IDS] ,
[PAGE IDS] ,
SUBSTRING([ParentObject],
CHARINDEX('Slot', [ParentObject]) + 4,
( CHARINDEX('Offset', [ParentObject])
- ( CHARINDEX('Slot', [ParentObject]) + 4 ) )
- 2) AS [Slot ID] ,
[AllocUnitId] ,
SUBSTRING(( SELECT
REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(':',
[Value]) + 1,
CHARINDEX('+',
- CHARINDEX(':',
[Value])), '+',
'')
@pagedata C
WHERE B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],
CHARINDEX('Slot',
B.[ParentObject])
( CHARINDEX('Offset',
B.[ParentObject])
- ( CHARINDEX('Slot',
B.[ParentObject])
+ 4 ) )) = SUBSTRING(C.[ParentObject],
CHARINDEX('Slot',
C.[ParentObject])
( CHARINDEX('Offset',
C.[ParentObject])
- ( CHARINDEX('Slot',
C.[ParentObject])
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],
CHARINDEX(':',
[Value]) - 1)
XML PATH('')
), 1, 1, ''), ' ', '')
), 1, 20000) AS [Value] ,
SUBSTRING(( SELECT
'0x'
+ REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(':',
[Value]) + 1,
CHARINDEX('+',
- CHARINDEX(':',
[Value])), '+',
'')
@pagedata C
B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],
CHARINDEX('Slot',
B.[ParentObject])
( CHARINDEX('Offset',
B.[ParentObject])
- ( CHARINDEX('Slot',
B.[ParentObject])
+ 4 ) )) = SUBSTRING(C.[ParentObject],
CHARINDEX('Slot',
C.[ParentObject])
( CHARINDEX('Offset',
C.[ParentObject])
- ( CHARINDEX('Slot',
C.[ParentObject])
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],
CHARINDEX(':',
[Value]) - 1)
XML PATH('')
), 1, 1, ''), ' ', '')
), 7, 4) AS [Length]
@pagedata B
[Object] LIKE '%Memory Dump%'
GROUP BY [Page ID] ,
[FILE IDS] ,
[PAGE IDS] ,
[ParentObject] ,
[AllocUnitId]--,[Current LSN]
ORDER BY [Slot ID]
@ModifiedRawData
[RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column(&[RowLog Length]&),0))',
'varbinary(Max)')))
@ModifiedRawData
[LINK ID] = 0
@ModifiedRawData
[RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column(&[RowLog Contents 0_var]&),0))',
'varbinary(Max)')
@ModifiedRawData
[LINK ID] = 0
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]
B.[Update] = ISNULL(B.[Update], 0) + 1
@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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
19 + 14, 2))))
AND A.[Link ID] = B.[Link ID]
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
27 + 14, 2))))
AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
31 + 14, 2))))
AND C.[Link ID] = B.[Link ID]
( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
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]
--,B.[Update]=ISNULL(B.[Update],0)+1
@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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
19 + 14, 2))))
AND A.[Link ID] && B.[Link ID]
AND B.[Update] = 0
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
27 + 14, 2))))
AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
31 + 14, 2))))
AND C.[Link ID] && B.[Link ID]
AND B.[Update] = 0
( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
@ModifiedRawData
[RowLog Contents 0] = ( CASE WHEN [RowLog Len] &= 8000
THEN SUBSTRING([RowLog Contents 0],
15, [RowLog Len])
WHEN [RowLog Len] & 8000
THEN SUBSTRING([RowLog Contents 0],
CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],
15, 6)))))
@ModifiedRawData
[LINK ID] = 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
17, 4)))) = [PAGE IDS]
AND CONVERT(INT, SUBSTRING([hex_value],
9, 2)) = B.[Link ID]
[System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
AND [Link ID] && 0
@ColumnNameAndData
[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]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
5, 4)))) = B.[PAGE IDS]
AND B.[Link ID] = 0
LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
17, 4)))) = C.[PAGE IDS]
AND C.[Link ID] = 0
[System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
AND ( B.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
9, 4)))) = [PAGE IDS]
AND CONVERT(INT, SUBSTRING([hex_value],
3, 2)) = [Link ID]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] && 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+10
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
9, 4)))) = [PAGE IDS]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] = 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
15, 4)))) = [PAGE IDS]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] = 0
@ColumnNameAndData
[hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, LEN([hex_value]))
--,[Update]=[Update]+1
[system_type_id] = 241
CREATE TABLE [#temp_Data]
[FieldName] VARCHAR(MAX) ,
[FieldValue] NVARCHAR(MAX) ,
[Rowlogcontents] VARBINARY(8000) ,
[Row ID] INT
INTO #temp_Data
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), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, 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, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME
WHEN system_type_id = 108
THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, 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, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)
+ CONVERT(VARBINARY(1), xscale))
+ CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMAL
WHEN system_type_id IN ( 60, 122 )
THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY
WHEN system_type_id = 104
THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value)
WHEN system_type_id = 62
THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))
+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
& 0x000FFFFFFFFFFFFF )
* POWER(CAST(2 AS FLOAT),
* POWER(CAST(2 AS FLOAT),
( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
& 0x7ff0 )
/ 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))
+ ( 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,
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)')
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,
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)') --TIMESTAMP
WHEN system_type_id = 98
THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,
LEN(hex_Value))))))
-- INTEGER
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,
20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
+ CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
+ CONVERT(VARBINARY(1), 0)
+ SUBSTRING(hex_Value, 5,
LEN(hex_Value)))) --- NUMERIC
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,
LEN(hex_Value))))) --VARCHAR,CHAR
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),
3, 20))) --UNIQUEIDENTIFIER
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,
LEN(hex_Value))))), 100) --DATETIME
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN '0x'
+ SUBSTRING(( CASE WHEN CHARINDEX(0x,
CAST('' AS XML).value('xs:hexBinary(sql:column(&hex_Value&))',
'VARBINARY(8000)')) = 0
THEN '0x'
ELSE ''
+ CAST('' AS XML).value('xs:hexBinary(sql:column(&hex_Value&))',
'varchar(max)'),
11, LEN(hex_Value)) -- BINARY,VARBINARY
END AS FieldValue ,
[Rowlogcontents] ,
@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))
syscolumns
id = OBJECT_ID(''
+ @SchemaName_n_TableName
+ '')
XML PATH('')
), 1, 1, '')
--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
恢复你的数据
--恢复数据，不加时间段条件
参数：数据库名，表名
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
--恢复数据，加时间段条件
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa','',''
执行了下面的存储过程之后你会发现会显示出刚才删除的数据
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
究竟他是如何工作的?让我们来一步一步来,这个过程涉及到7个步骤:
我们需要获得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 (‘LCX_MARK_AS_GHOST’and&‘LCX_HEAP’)Operation (‘LOP_DELETE_ROWS’)AllocUnitName(‘dbo.aa’) –- Schema + table Name
Context可以说明是堆表还是聚集表
Operation：删除操作
AllocUnitName：分配单元名称，表名
下面是一个代码片段
[RowLog Contents 0]
sys.fn_dblog(NULL, NULL)
AllocUnitName = 'dbo.aa'
AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )
AND Operation IN ( 'LOP_DELETE_ROWS' )
这个查询会返回不同列的信息，但是我们只需要选择[RowLog Contents 0]列，去获得被删除的数据的内容
RowLog&content 0列的内容类似于这样
“0x006B5900000
剩余步骤请参照一下链接：
/lyhabc/p/3683147.html
公司DBA说这个方法是几年前的老方法了，如果删除数据量大的话可能恢复不了或者只能回复部分数据。
恢复数据最好的方法还是定期备份，根据备份还原。
上面说的方法我也试过了，按照他的步骤确实可以进行恢复。但如果时删除3000+数据的话，跑了2个多小时，最后报错退出，一条都没有恢复成功！
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：312900次
积分：3199
积分：3199
排名：第9466名
原创：90篇
转载：35篇
评论：17条
(1)(2)(2)(1)(1)(2)(1)(4)(1)(1)(1)(1)(1)(1)(3)(3)(5)(1)(6)(3)(5)(3)(2)(1)(5)(2)(4)(7)(9)(21)(11)(15)}
sql数据库delete删除后怎么恢复，这是网上找的答案。。希望大神验证指教一下 - 洛晨随风
使用Log Explorer查看和恢复数据
Log Explorer 4.1.可用于SQL Server2005的日志查看工具
http://download.csdn.net/source/620271
打开Log Explorer -& Attach Log File -& 选择SQL Server服务器和登陆方式 -& Connect -&
在Database Name中选择数据库 -& Attach-& 左面对话框中Browse-& View Log-& 就可以看到log记录了
想恢复的话: 右键Log记录 Undo Transation-& 选择保存文件名和路径-& 然后打开该文件到查询分析器里执行
T-sql代码就可以了
例如 如果Log是delete table where ...的话,生成的文件代码就是insert table ....
然后将此insert table的代码放到查询分析器里执行.就可以恢复数据.
----------------------------------------------------------------------
--如何恢复被delete/update的数据
----------------------------------------------------------------------
1 连接到被删除数据库的Db
打开log explorer
&file&-&&attach log file&-&选择服务器和登陆方式-&&connect&-&选择&数据库&-&&attach&
在左面操作项目的对话框中选择&browse&项目-&&view log&-&就可以看到当前的Log记录了
右键某一条log记录,选择&undo transation&-&&选择保存文件名和路径&-&然后打开该文件到查询分析器里执行
T-sql代码就可以了
例如: 如果log是delete table where ...的话,生成的文件代码就是insert table ....
----------------------------------------------------------------------
--Log Explorer恢复被drop table和truncate table后的数据
----------------------------------------------------------------------
连接到被删除数据库的Db
选择&salvaage dropped/truncate&菜单,在右边的对话框中选择表名,和droped/trucated的日期,
File Name中选择生成insert语句脚步的存放位置,condition选择是droped还是truncated,
最后点击&create& 就会生成insert语句,把生成的语句到查询分析器里面执行一下就可以了
2) 选择&ViewDDL Commands&菜单-&选&truncate table& 操作项-&点击&Salvage&-&生成语句-&查询分析器里执行
----------------------------------------------------------------------
--log explorer使用的几个问题
----------------------------------------------------------------------
1) 对数据库做完全/差异/日志备份
备份时如果选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时,提示No log recorders found that match the filter，would you like to view unfiltered data 选择yes 就看不到刚才的记录了
如果不选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时，就能看到原来的日志并做恢复
修改了其中一个表中的部分数据，此时用Log explorer看日志，可以作日志恢复
然后恢复备份，(注意:恢复是断开log explorer与数据库的连接，或连接到其他数据上，
否则会出现数据库正在使用无法恢复)
恢复完后，再打开log explorer 提示No log recorders found that match the filter，would you like to view unfiltered data,选择yes 就看不到刚才在2中修改的日志记录，所以无法做恢复.
不要用SQL的备份功能备份,搞不好你的日志就破坏了.
正确的备份方法是:
停止SQL服务,复制数据文件及日志文件进行文件备份.
然后启动SQL服务,用log explorer恢复数据
已发表评论数()
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
主题不准确
没有分页内容
图片无法显示
视频无法显示
与原文不一致oracle中及时找回被删除的数据
oracle中及时找回被删除的数据
select * from whir$t3068 as of timestamp to_timestamp( ' 9:00:00' , 'yyyy-mm-dd hh24:mi:ss' )&
select * from whir$t3068 as of timestamp sysdate-750/1440;&
flashBack table scheme.whir$t3068 to timestamp(timeVariable,' 09:mi:ss')&
********************这个效果很好***************************&
先执行SQL查询出来被删除的数据&
select * from 表名 as of timestamp sysdate-1/12&
在把数据添加到删除的表中&
insert into biao select &* from 表名 as of timestamp sysdate-1/12&Sql Server2005数据库误删除表恢复方法
您现在位置： &&
Sql Server2005数据库误删除表恢复方法
假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE Db.dbo.TB_test
--保存删除表的时间
SELECT dt=GETDATE() INTO #
--在删除操作后,发现不应该删除表 Db.dbo.TB_test
--下面演示了如何恢复这个误删除的表 Db.dbo.TB_test
--首先,备份事务日志(使用事务日志才能还原到指定的时间点)
BACKUP LOG Db TO DISK='c:\db_log.bak' WITH FORMAT
--接下来,我们要先还原完全备份(还原日志必须在还原完全备份的基础上进行)
RESTORE DATABASE Db FROM DISK='c:\db.bak' WITH REPLACE,NORECOVERY
--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM #
--获取比表被删除的时间略早的时间
RESTORE LOG Db FROM DISK='c:\db_log.bak' WITH RECOVERY,STOPAT=@dt
--查询一下,看表是否恢复
SELECT * FROM Db.dbo.TB_test
-----------
（所影响的行数为 0 行）
--测试成功
飞客数据恢复中心
飞客咨询电话 400-779-66963883人阅读
数据库（3）
曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据
这里有一篇文章做到了，不过似乎不是所有的数据类型都支持
以下为译文：
在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”
现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据
（注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）
imagetextuniqueidentifiertinyintsmallintintsmalldatetimerealmoneydatetimefloatsql_variantntextbitdecimalnumericsmallmoneybigintvarbinaryvarcharbinarychartimestampnvarcharncharxmlsysname
让我来用demo来解释一下我是怎么做到的
USE master
--创建数据库
CREATE DATABASE test
USE [test]
CREATE TABLE [dbo].[aa](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](200) NULL
) ON [PRIMARY]
--插入测试数据
INSERT [dbo].[aa]
( [NAME] )
SELECT '你好'
--删除数据
Delete from aa
--验证数据是否已经删除
Select * from aa
现在你需要创建一个存储过程来恢复你的数据
-- 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
-- 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 = '' ,
@Date_To DATETIME = ''
DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Compatibility_Level INT
@Compatibility_Level = patibility_level
master.sys.databases AS dtb
dtb.name = @Database_Name
IF ISNULL(@Compatibility_Level, 0) &= 80
RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
IF ( SELECT COUNT(*)
INFORMATION_SCHEMA.TABLES
[TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName
RAISERROR('Could not found the table in the defined database',16,1)
DECLARE @bitTable TABLE
[ID] INT ,
[Bitvalue] INT
--Create table to set the bit position of one byte.
INTO @bitTable
--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.
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))))
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))
( 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))))
END ) * 2)
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))))
END ) * 2 ) )
END AS [VarColumnStart] ,
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
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
@Date_To )
--Use this technique to repeate the row till the no of bytes of the row.
AS ( SELECT
AS ( SELECT
AS ( SELECT
AS ( SELECT
ROW_NUMBER() OVER ( ORDER BY X.n )
INTO @DeletedRecords
RowLogContents ,
[AllocUnitID] ,
[Transaction ID] ,
[FixedLengthData] ,
[TotalNoOfCols] ,
[NullBitMapLength] ,
[NullBytes] ,
[TotalNoofVarCols] ,
[ColumnOffsetArray] ,
[VarColumnStart] ,
---Get the Null value against each column (1 means null zero means not null)
[NullBitMap] = ( REPLACE(STUFF(( SELECT
','
WHEN [ID] = 0
THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes,
n, 1) % 2 ))
ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes,
/ [Bitvalue] )
END ) --as [nullBitMap]
FROM N4 AS Nums
JOIN RowData AS C ON n &= NullBitMapLength
CROSS JOIN @bitTable
C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents] ,
XML PATH('')
), 1, 1, ''), ',', '') )
IF ( SELECT COUNT(*)
@DeletedRecords
RAISERROR('There is no data in the log as per the search criteria',16,1)
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
INTO @ColumnNameAndData
[Row ID] ,
Rowlogcontents ,
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,
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- POWER(2, 15)
ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
END ) AS [Column value Size] ,
( CASE WHEN leaf_offset & 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit,
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 ) THEN 16
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 ) THEN 16
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) )
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) AS [Column Length] ,
( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1
ELSE SUBSTRING(Rowlogcontents,
( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- POWER(2, 15)
ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE
WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE
WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) ) + 1,
( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ( CASE WHEN [System_type_id] IN (
35, 34, 99 )
END ) --24
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]))
WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2)))) & 30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart]) & 30000
THEN POWER(2, 15)
+ CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* leaf_offset
* -1 ) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
* ( ( leaf_offset
* -1 ) - 1 ) )
- 1, 2)))), 0),
[varColumnStart])
END ) AS hex_Value ,
[Slot ID] ,
@DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
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
leaf_offset & 0
/*This part is for fixed data columns*/
[Row ID] ,
Rowlogcontents ,
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 ,
( SELECT TOP 1
ISNULL(SUM(CASE WHEN C.leaf_offset & 1
THEN max_length
sys.system_internals_partition_columns C
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], cols.leaf_null_bit, 1) = 1
ELSE SUBSTRING(Rowlogcontents,
( SELECT TOP 1
ISNULL(SUM(CASE
WHEN C.leaf_offset & 1
AND C.leaf_bit_position = 0
THEN max_length
sys.system_internals_partition_columns C
cols.partition_id = C.partition_id
AND C.leaf_null_bit & cols.leaf_null_bit
) + 5, syscolumns.length)
END AS hex_Value ,
[Slot ID] ,
@DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
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
leaf_offset & 0
ORDER BY nullbit
DECLARE @BitColumnByte AS INT
@BitColumnByte = CONVERT(INT, CEILING(COUNT(*) / 8.0))
@ColumnNameAndData
[System_Type_id] = 104;
AS ( SELECT
AS ( SELECT
AS ( SELECT
AS ( SELECT
ROW_NUMBER() OVER ( ORDER BY X.n )
AS ( SELECT
RowLogContents ,
[nullbit] ,
[BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT
','
WHEN [ID] = 0
THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value,
n, 1) % 2 ))
ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value,
/ [Bitvalue] )
END ) --as [nullBitMap]
N4 AS Nums
JOIN @ColumnNameAndData
AS C ON n &= @BitColumnByte
AND [System_Type_id] = 104
AND bitpos = 0
CROSS JOIN @bitTable
C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents] ,
PATH('')
), 1, 1, ''),
',', '') ),
bitpos + 1, 1)))
@ColumnNameAndData D
[System_Type_id] = 104
[hex_Value] = [BitMap]
@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 ,
[Object] SYSNAME ,
[Field] SYSNAME ,
[Value] SYSNAME
DECLARE @ModifiedRawData TABLE
[ID] INT IDENTITY(1, 1) ,
[PAGE ID] VARCHAR(MAX) ,
[FILE IDS] INT ,
[PAGE IDS] INT ,
[Slot ID] INT ,
[AllocUnitId] BIGINT ,
[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
/*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*/
LTRIM(RTRIM(REPLACE([Description], 'Deallocated', ''))) AS [PAGE ID] ,
[Slot ID] ,
[AllocUnitId] ,
NULL AS [RowLog Contents 0] ,
NULL AS [RowLog Contents 0] ,
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
Context IN ( 'LCX_NULL' )
AND Operation IN ( 'LOP_BEGIN_XACT' )
AND [Transaction Name] = 'DELETE'
AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
@Date_To )
GROUP BY [Description] ,
[Slot ID] ,
[AllocUnitId] ,
[PAGE ID] ,
[Slot ID] ,
[AllocUnitId] ,
SUBSTRING([RowLog Contents 0], 15,
LEN([RowLog Contents 0])) AS [RowLog Contents 0] ,
CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) ,
Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
sys.fn_dblog(NULL, NULL)
AllocUnitId IN (
[Allocation_unit_id]
sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
AND partitions.hobt_id = allocunits.container_id
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
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]
sys.fn_dblog(NULL, NULL)
Context IN ( 'LCX_NULL' )
AND Operation IN ( 'LOP_BEGIN_XACT' )
AND [Transaction Name] = 'DELETE'
AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
@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
DECLARE @hex_pageid AS VARCHAR(MAX)
/*Page ID contains File Number and page number It looks like 0.
In this example 0001 is file Number &
is Page Number & These numbers are in Hex format*/
SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0,
CHARINDEX(':', @ConsolidatedPageID)) -- Seperate File ID from Page ID
SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,
CHARINDEX(':',
@ConsolidatedPageID)
+ 1, LEN(@ConsolidatedPageID))
---Seperate the page ID
@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
CASE SUBSTRING(@hex_pageid, 1, 2)
WHEN '0x' THEN 3
) AS t ( pos )
IF @Context = 'LCX_PFS'
@temppagedata
INTO @temppagedata
( 'DBCC PAGE(' + @DataBase_Name + ', '
+ @fileid + ', ' + @pageid
+ ', 1) with tableresults,no_'
INTO @pagedata
@ConsolidatedPageID ,
@AllocUnitID ,
[ParentObject] ,
[Object] ,
@temppagedata
IF @Context = 'LCX_TEXT_MIX'
INTO @ModifiedRawData
@ConsolidatedPageID ,
@AllocUnitID ,
CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,
11, 2)))) ,
@LCX_TEXT_MIX ,
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
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
INTO @ModifiedRawData
( [PAGE ID] ,
[FILE IDS] ,
[PAGE IDS] ,
[Slot ID] ,
[AllocUnitId] ,
[RowLog Contents 0_var] ,
[RowLog Length]
[Page ID] ,
[FILE IDS] ,
[PAGE IDS] ,
SUBSTRING([ParentObject],
CHARINDEX('Slot', [ParentObject]) + 4,
( CHARINDEX('Offset', [ParentObject])
- ( CHARINDEX('Slot', [ParentObject]) + 4 ) )
- 2) AS [Slot ID] ,
[AllocUnitId] ,
SUBSTRING(( SELECT
REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(':',
[Value]) + 1,
CHARINDEX('+',
- CHARINDEX(':',
[Value])), '+',
'')
@pagedata C
WHERE B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],
CHARINDEX('Slot',
B.[ParentObject])
( CHARINDEX('Offset',
B.[ParentObject])
- ( CHARINDEX('Slot',
B.[ParentObject])
+ 4 ) )) = SUBSTRING(C.[ParentObject],
CHARINDEX('Slot',
C.[ParentObject])
( CHARINDEX('Offset',
C.[ParentObject])
- ( CHARINDEX('Slot',
C.[ParentObject])
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],
CHARINDEX(':',
[Value]) - 1)
XML PATH('')
), 1, 1, ''), ' ', '')
), 1, 20000) AS [Value] ,
SUBSTRING(( SELECT
'0x'
+ REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(':',
[Value]) + 1,
CHARINDEX('+',
- CHARINDEX(':',
[Value])), '+',
'')
@pagedata C
B.[Page ID] = C.[Page ID]
AND SUBSTRING(B.[ParentObject],
CHARINDEX('Slot',
B.[ParentObject])
( CHARINDEX('Offset',
B.[ParentObject])
- ( CHARINDEX('Slot',
B.[ParentObject])
+ 4 ) )) = SUBSTRING(C.[ParentObject],
CHARINDEX('Slot',
C.[ParentObject])
( CHARINDEX('Offset',
C.[ParentObject])
- ( CHARINDEX('Slot',
C.[ParentObject])
AND [Object] LIKE '%Memory Dump%'
ORDER BY '0x'
+ LEFT([Value],
CHARINDEX(':',
[Value]) - 1)
XML PATH('')
), 1, 1, ''), ' ', '')
), 7, 4) AS [Length]
@pagedata B
[Object] LIKE '%Memory Dump%'
GROUP BY [Page ID] ,
[FILE IDS] ,
[PAGE IDS] ,
[ParentObject] ,
[AllocUnitId]--,[Current LSN]
ORDER BY [Slot ID]
@ModifiedRawData
[RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column(&[RowLog Length]&),0))',
'varbinary(Max)')))
@ModifiedRawData
[LINK ID] = 0
@ModifiedRawData
[RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column(&[RowLog Contents 0_var]&),0))',
'varbinary(Max)')
@ModifiedRawData
[LINK ID] = 0
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]
B.[Update] = ISNULL(B.[Update], 0) + 1
@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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
19 + 14, 2))))
AND A.[Link ID] = B.[Link ID]
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
27 + 14, 2))))
AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
31 + 14, 2))))
AND C.[Link ID] = B.[Link ID]
( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
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]
--,B.[Update]=ISNULL(B.[Update],0)+1
@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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
19 + 14, 2))))
AND A.[Link ID] && B.[Link ID]
AND B.[Update] = 0
LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
27 + 14, 2))))
AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
31 + 14, 2))))
AND C.[Link ID] && B.[Link ID]
AND B.[Update] = 0
( A.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
@ModifiedRawData
[RowLog Contents 0] = ( CASE WHEN [RowLog Len] &= 8000
THEN SUBSTRING([RowLog Contents 0],
15, [RowLog Len])
WHEN [RowLog Len] & 8000
THEN SUBSTRING([RowLog Contents 0],
CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],
15, 6)))))
@ModifiedRawData
[LINK ID] = 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
17, 4)))) = [PAGE IDS]
AND CONVERT(INT, SUBSTRING([hex_value],
9, 2)) = B.[Link ID]
[System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
AND [Link ID] && 0
@ColumnNameAndData
[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]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
5, 4)))) = B.[PAGE IDS]
AND B.[Link ID] = 0
LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
17, 4)))) = C.[PAGE IDS]
AND C.[Link ID] = 0
[System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
AND ( B.[RowLog Contents 0] IS NOT NULL
OR C.[RowLog Contents 0] IS NOT NULL
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
9, 4)))) = [PAGE IDS]
AND CONVERT(INT, SUBSTRING([hex_value],
3, 2)) = [Link ID]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] && 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+10
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
9, 4)))) = [PAGE IDS]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] = 0
@ColumnNameAndData
[hex_Value] = [RowLog Contents 0]
--,A.[Update]=A.[Update]+1
@ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
15, 4)))) = [PAGE IDS]
[System_Type_Id] IN ( 35, 34, 99 )
AND [Link ID] = 0
@ColumnNameAndData
[hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, LEN([hex_value]))
--,[Update]=[Update]+1
[system_type_id] = 241
CREATE TABLE [#temp_Data]
[FieldName] VARCHAR(MAX) ,
[FieldValue] NVARCHAR(MAX) ,
[Rowlogcontents] VARBINARY(8000) ,
[Row ID] INT
INTO #temp_Data
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), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, 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, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME
WHEN system_type_id = 108
THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, 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, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)
+ CONVERT(VARBINARY(1), xscale))
+ CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMAL
WHEN system_type_id IN ( 60, 122 )
THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY
WHEN system_type_id = 104
THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value)
WHEN system_type_id = 62
THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))
+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
& 0x000FFFFFFFFFFFFF )
* POWER(CAST(2 AS FLOAT),
* POWER(CAST(2 AS FLOAT),
( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
& 0x7ff0 )
/ 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))
+ ( 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,
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)')
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,
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)') --TIMESTAMP
WHEN system_type_id = 98
THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,
LEN(hex_Value))))))
-- INTEGER
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,
20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
+ CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
+ CONVERT(VARBINARY(1), 0)
+ SUBSTRING(hex_Value, 5,
LEN(hex_Value)))) --- NUMERIC
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,
LEN(hex_Value))))) --VARCHAR,CHAR
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),
3, 20))) --UNIQUEIDENTIFIER
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,
LEN(hex_Value))))), 100) --DATETIME
WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
THEN '0x'
+ SUBSTRING(( CASE WHEN CHARINDEX(0x,
CAST('' AS XML).value('xs:hexBinary(sql:column(&hex_Value&))',
'VARBINARY(8000)')) = 0
THEN '0x'
ELSE ''
+ CAST('' AS XML).value('xs:hexBinary(sql:column(&hex_Value&))',
'varchar(max)'),
11, LEN(hex_Value)) -- BINARY,VARBINARY
END AS FieldValue ,
[Rowlogcontents] ,
@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))
syscolumns
id = OBJECT_ID(''
+ @SchemaName_n_TableName
+ '')
XML PATH('')
), 1, 1, '')
--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
恢复你的数据
--恢复数据，不加时间段条件
参数：数据库名，表名
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
--恢复数据，加时间段条件
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa','',''
执行了下面的存储过程之后你会发现会显示出刚才删除的数据
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
究竟他是如何工作的?让我们来一步一步来,这个过程涉及到7个步骤:
我们需要获得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 (‘LCX_MARK_AS_GHOST’and&‘LCX_HEAP’)Operation (‘LOP_DELETE_ROWS’)AllocUnitName(‘dbo.aa’) –- Schema + table Name
Context可以说明是堆表还是聚集表
Operation：删除操作
AllocUnitName：分配单元名称，表名
下面是一个代码片段
[RowLog Contents 0]
sys.fn_dblog(NULL, NULL)
AllocUnitName = 'dbo.aa'
AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )
AND Operation IN ( 'LOP_DELETE_ROWS' )
这个查询会返回不同列的信息，但是我们只需要选择[RowLog Contents 0]列，去获得被删除的数据的内容
RowLog&content 0列的内容类似于这样
“0x006B5900000
剩余步骤请参照一下链接：
/lyhabc/p/3683147.html
公司DBA说这个方法是几年前的老方法了，如果删除数据量大的话可能恢复不了或者只能回复部分数据。
恢复数据最好的方法还是定期备份，根据备份还原。
上面说的方法我也试过了，按照他的步骤确实可以进行恢复。但如果时删除3000+数据的话，跑了2个多小时，最后报错退出，一条都没有恢复成功！
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：312900次
积分：3199
积分：3199
排名：第9466名
原创：90篇
转载：35篇
评论：17条
(1)(2)(2)(1)(1)(2)(1)(4)(1)(1)(1)(1)(1)(1)(3)(3)(5)(1)(6)(3)(5)(3)(2)(1)(5)(2)(4)(7)(9)(21)(11)(15)}
我要回帖
更多关于 求助别人带做生意什 的文章
更多推荐
- ·声援望哨的近义词词和反义词是什么_声援是什么意思?
- ·手机如何登录foxmail邮箱有安卓app吗
- ·有哪位老板买过亿堡的十大坚果零食品牌排行榜原料吗？跟别的品牌有区别吗
- ·哪个封边机什么牌子的性价比高辅料贴装机好？有推荐吗？
- ·中邮消费循环贷金可以线上贷款吗？
- ·求《Remember-儿子的战争评价》和韩版《步步惊心:丽》的百度云盘下载资源
- ·富怡服装cad教程CAD能连接普通的绘图机吗
- ·涿州注册公司苗蒲公司好办吗
- ·杳看别人投诉我微信投诉单号查询号
- ·如何判断一个网页是否出现404错误
- ·如何判断一个网页是否出现404错误
- ·注册资金如何让十万变成一百万和一百万有什么区别，注册资金高的利弊是什么
- ·怎么在phpstorm里查找编辑好链接的文件不可用 要编辑链接
- ·2-10第一问怎么算？
- ·汇编中的int3中断汇编中equ是什么意思思
- ·正常启动，启动不了，重装系统到一半就死机了，怎么办
- ·正常启动，启动不了，重装系统到一半就死机了，怎么办
- ·怎样把词向量文件转换为lmdb是什么格式格式
- ·中国电信董事长 杨杰杨杰妻子
- ·学习C语言需要英语，数学的基础吗
- ·求助别人带做生意什，SQL数据被别人删除了，怎么找回来
- ·加密拉架加密文件名是什么意思思
- ·申花柏佳骏女友老婆
- ·杨大卫85式太极拳精讲扬式太极拳音乐名
- ·飞线纠结是nike飞线的还是李宁的
- ·什么是单跑楼梯，什么是双跑楼梯是什么意思
- ·什么是单跑楼梯跑，什么是双跑楼梯跑
- ·最强nba拉塞尔篮板最高多少真的比克拉和兰德尔强很多吗
- ·拉塞尔真的比克拉和兰德尔.朴强很多吗
- ·刚开始跑步小腿胀痛
- ·三亚4月份天气的天气适合养伤吗
- ·捕鱼回馈赛真没什么技巧
- ·宝宝额头肌肉上有块皮长好了,但是里面肌肉没有长好怎么
- ·曼妙纤美是罗玉凤在美国做什么么的？
