Feeds:
Posts
Comments

Archive for the ‘SQL Server Recovery Tools’ Category

Sometimes, it is very important to know when your database has been dropped as well who dropped it. Obviously, if you setup the database backup properly, you can easily recover it from the last backup but how to find who dropped/ deleted the database ? Today, I came across this issue and I started my research and found some solutions to recover this info using trace, however, I developed a script that will help you find who dropped database, at what time, by using SQL Server Log.

Note : Please do not use this script for any negative purpose.

Script :

--This script is compatible with SQL Server 2005 and above.
USE master
GO
DROP PROCEDURE Recover_Dropped_Database_Detail_Proc
GO
CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
;WITH CTE AS (
Select REPLACE(SUBSTRING(A.[RowLog Contents 0],9
,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Database Name]
,[Transaction ID]
FROM fn_dblog(NULL,NULL) A
WHERE A.[AllocUnitName] ='sys.sysdbreg.nc1'AND
A.[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] LIKE '%dbdestroy%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))

SELECT
 A.[Database Name]
,B.[Begin Time] AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%dbdestroy%'
INNER JOIN sys.sysusers C ON B.[Transaction SID]=C.[Sid]

GO
EXEC Recover_Dropped_Database_Detail_Proc
GO

1

Let me know if you came across this issue and its solution as well.

Read Full Post »

Last day of the last year, I came across a situation where I was stuck for a while even though we had backups. However later on found a solution and felt so excited ;).
Note : Please do not use this script for any negative purpose.

Problem :
As I mentioned earlier that last year on 31st Dec 2013, we were busy helping our client for yearend closing and suddenly we got an error from year closing procedure. Given below are the details.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘tbl_Yeartodate_Value’.

Now, the problem is this table gets updated every evening and we do not why it is not available in the database. We do have each and everyday backup. However, we do not know when it was dropped and who dropped it. We started looking for the solution because it is not possible to restore all backups and check one by one.

Solution :
Some of our support engineers proposed the third party solution to recover the table directly because we do not know in which backup this table exists. I bought some time from the client and started doing my development for recovery and after few hours, I developed a script that could give the dropped object name along with the dropped date and dropped by name as well. However, I made sure that the client will not take any action against the person who dropped it.

Let me create few objects and drop it to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--Create Schema
CREATE SCHEMA HumanResources
GO

--Create Table
CREATE TABLE HumanResources.[tbl_Employee](
      [Sno] [int] NOT NULL,
      [ID] nvarchar(6) Not NULL ,
      [name] [varchar](50) NOT NULL,
      [Date of Birth]  datetime not null,
      [Weight] [int] NULL)

GO

--Create View
Create View HumanResources.[vw_Employee]
as
Select * from HumanResources.[tbl_Employee]
GO

--Create Procedure
Create Procedure HumanResources.[usp_Employee]
@EmployeeID nvarchar(6)
as
Select * from HumanResources.[tbl_Employee] Where [ID] =@EmployeeID
GO

--Create function
Create FUNCTION HumanResources.[fn_Employee](@EmployeeID nvarchar(6))
RETURNS int
AS
Begin
    Declare @Weight int
    Select  @Weight = [Weight]
        from HumanResources.[tbl_Employee] Where ID =@EmployeeID
    Return  @Weight
End
GO

--Create Trigger
CREATE TRIGGER HumanResources.trg_Employee
ON [tbl_Employee]
FOR INSERT
AS RAISERROR (50009, 16, 10)
GO

--Drop all objects
DROP TRIGGER HumanResources.trg_Employee
DROP VIEW HumanResources.[vw_Employee]
DROP PROCEDURE HumanResources.[usp_Employee]
DROP FUNCTION HumanResources.[Fn_Employee]
DROP TABLE HumanResources.[tbl_Employee]
GO

Once you dropped all object, you need to create the given below script to recover who dropped what object at what time.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP PROCEDURE Recover_Dropped_Objects_Detail_Proc
--GO
CREATE PROCEDURE Recover_Dropped_Objects_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

;WITH CTE AS (Select B.name AS [Schema Name]
,REPLACE(SUBSTRING(A.[RowLog Contents 0],14,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Object Name]
,[Transaction ID]
,A.[RowLog Contents 0]
FROM fn_dblog(NULL,NULL) A
LEFT JOIN sys.schemas B
ON CONVERT(INT,SUBSTRING([RowLog Contents 0],2,2))= B.schema_id
WHERE A.[AllocUnitName] ='sys.sysschobjs.nc1'AND
A.[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] LIKE '%DROP%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))

SELECT
[Schema Name]
,[Object Name]
,B.[Begin Time]  AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name]LIKE '%DROP%'
INNER JOIN sys.sysusers  C ON B.[Transaction SID]=C.[Sid]
GO

Once you created the above stored procedure, lets execute it and get all the object names dropped during a particular interval, along with its dropped date and dropped by name.
Example :

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
EXEC Recover_Dropped_Objects_Detail_Proc  '2014/02/26','2014/02/26'
GO
--OUTPUT

who_dropped_what_object_at_what_time.1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

Yesterday, I was browsing few objects in SSMS in my test SQL server database and by mistake, I did a single click on an object and typed something and it got renamed and disappered from the search result. Now, what and who I renamed? I wasn’t sure 😦  Fortunately, I had a backup and I could restore and recover my objects. But what if I did not have a backup and it happens on production database ? a nightmare I’d say?
Last year, I developed a tool that usually recovers the accidentally renamed table name but it does not give you the object type. After this incident, I started with my research and modified the script and now it can recover any renamed object with its object type as well.

Note : It will only recover the object name if you renamed it through SSMS.

Given below is the stored procedure that can recover any object name.

--DROP PROCEDURE Recover_Renamed_Object_Name_Proc
--GO
CREATE PROCEDURE Recover_Renamed_Object_Name_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
Select type_desc As [Object Type]
,REPLACE(Substring(A.[RowLog Contents 0]
,14,LEN(A.[RowLog Contents 0])),0x00,0x) as [Object Name Before Rename]
,REPLACE(Substring(B.[RowLog Contents 0]
,14,LEN(B.[RowLog Contents 0])),0x00,0x) as [Object Name After Rename]
FROM sys.fn_dblog(NULL, NULL) A
Inner Join sys.fn_dblog(NULL, NULL) B 
On A.[Transaction ID]=B.[Transaction ID]
And A.AllocUnitId = B.AllocUnitId
Left Join sys.objects S On CONVERT(BIGINT,CONVERT(VARBINARY(MAX)
,REVERSE(Substring(A.[RowLog Contents 0],7,4))))=S.object_id
WHERE
A.AllocUnitId IN (562949955649536) AND A.Context IN ('LCX_MARK_AS_GHOST')
AND A.Operation IN ('LOP_DELETE_ROWS') AND B.Context IN ('LCX_INDEX_LEAF')
AND B.Operation IN ('LOP_INSERT_ROWS') 
/*Use this subquery to filter the date*/
AND A.[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]='user_transaction' 
AND CONVERT(NVARCHAR(11),[Begin Time]) 
BETWEEN @Date_From AND @Date_To)
GO
--Example EXEC Recover_Renamed_Object_Name_Proc 'yyyy/MM/dd','yyyy/MM/dd'
EXEC Recover_Renamed_Object_Name_Proc '2013/10/31','2014/12/31'
--OUTPUT

recover renamed object name

In case you do not know the modified date & time of any object, you can write given below query and use [modify_date] column.

Select name, type_desc, modify_date from sys.objects
Order by Modify_date DESC

Read Full Post »

After publishing a solution how to recover the modified / updated records without backup in one of my previous articles, I received lot of queries inquiring how to find who modified / updated and at what time ? So, I started my research and recently developed the solution.

Before proceeding with the solution, let me create a sample to demonstrate the solution.
Given below is the script.

--Create Table
CREATE TABLE [dbo].[Student](
      [Sno] [int] NOT NULL,
      [Student ID] nvarchar(6) Not NULL ,
      [Student name] [varchar](50) NOT NULL,
      [Date of Birth]  datetime not null,
      [Weight] [int] NULL)
GO
--Inserting data into table

Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40)
Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',35)
GO

--Check the existence of the data
Select * from dbo.[Student]
GO
--OUTPUT

Who modified at what time 1.1

--Modified the date by mistake without where clause
Update [Student] Set [Student Name]='Bob jerry'
GO

--Verify the data has been modified
Select * from dbo.[Student]

GO
--OUTPUT

Who modified at what time 1.2

Solution :
Given below is the stored procedure that you need to execute in the database having modified records.

--DROP PROCEDURE Recover_Modified_Data_Proc
--GO
Create PROCEDURE Recover_Modified_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 @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @RowLogContents0 VARBINARY(8000)
DECLARE @RowLogContents1 VARBINARY(8000)
DECLARE @RowLogContents3 VARBINARY(8000)
DECLARE @RowLogContents3_Var VARCHAR(MAX)
 
DECLARE @RowLogContents4 VARBINARY(8000)
DECLARE @LogRecord VARBINARY(8000)
DECLARE @LogRecord_Var VARCHAR(MAX)
 
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @TransactionID VARCHAR(MAX)
DECLARE @TrID SYSNAME
DECLARE @Operation VARCHAR(MAX)
DECLARE @DatabaseCollation VARCHAR(MAX)
 
 
/*  Pick The actual data
*/
DECLARE @temppagedata TABLE
(
[ParentObject] SYSNAME,
[Object] SYSNAME,
[Field] SYSNAME,
[Value] SYSNAME)
 
DECLARE @pagedata TABLE
(
[Page ID] SYSNAME,
[AllocUnitId] BIGINT,
[Tr ID] SYSNAME,
[ParentObject] SYSNAME,
[Object] SYSNAME,
[Field] SYSNAME,
[Value] SYSNAME)
 
 
    DECLARE Page_Data_Cursor CURSOR FOR
    /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
    SELECT [PAGE ID],[Slot ID],[AllocUnitId],[Transaction 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 Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')  AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
    /*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]='UPDATE'
    AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 
    /****************************************/
 
    GROUP BY [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID]
    ORDER BY [Slot ID]    
  
    OPEN Page_Data_Cursor
 
    FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TrID
 
    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,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
        SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@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, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) 
                     
        DELETE @temppagedata
        -- Now we need to get the actual data (After modification) from the page
        INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;'); 
        -- Add Page Number and allocUnit ID in data to identity which one page it belongs to.                    
        INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,@TrID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
 
        FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID,@TrID
    END
 
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
 
DECLARE @Newhexstring VARCHAR(MAX);
 

DECLARE @ModifiedRawData TABLE
(
  [ID] INT IDENTITY(1,1),
  [PAGE ID] VARCHAR(MAX),
  [Slot ID] INT,
  [AllocUnitId] BIGINT,
  [Tr ID] sysname,
  [RowLog Contents 0_var] VARCHAR(MAX),
  [RowLog Contents 0] VARBINARY(8000)
)
--The modified 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],[Slot ID],[AllocUnitId],[Tr ID]
,[RowLog Contents 0_var])
SELECT B.[PAGE ID],A.[Slot ID],A.[AllocUnitId],A.[Transaction ID]
,(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([VALUE],CHARINDEX(':',[Value])+1,48),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And A.[Slot ID] =LTRIM(RTRIM(SUBSTRING(C.[ParentObject],5,3))) And [Object] Like '%Memory Dump%'
Group By [Value] FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]
 
FROM sys.fn_dblog(NULL, NULL) A
INNER JOIN @pagedata B On A.[PAGE ID]=B.[PAGE ID]
AND A.[AllocUnitId]=B.[AllocUnitId]
AND A.[Slot ID] =LTRIM(RTRIM(Substring(B.[ParentObject],5,3)))
AND B.[Object] Like '%Memory Dump%'
WHERE A.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','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*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]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 
/****************************************/
GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId],A.[Transaction ID]
ORDER BY [Slot ID]
 
 
-- Convert the hex value data in string, convert it into Hex value as well. 
UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
FROM @ModifiedRawData
 
---Now we have modifed data plus its slot ID , page ID and allocunit as well.
--After that we need to get the old values before modfication, these datas are in chunks.
DECLARE Page_Data_Cursor CURSOR FOR
 
SELECT [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4]
,SUBSTRING ([Log Record],[Log Record Fixed Length],([Log Record Length]+1)-([Log Record Fixed Length])) AS [Log Record]
,Operation
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','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*Use this sub query 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]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 
/****************************************/
ORDER BY [Slot ID],[Transaction ID] DESC
 
OPEN Page_Data_Cursor
 
    FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
    WHILE @@FETCH_STATUS = 0
    BEGIN
           IF @Operation ='LOP_MODIFY_ROW' 
              BEGIN
                      /* If it is @Operation Type is 'LOP_MODIFY_ROW' then it is very simple to recover the modified data. The old data is in [RowLog Contents 0] Field and modified data is in [RowLog Contents 1] Field. Simply replace it with the modified data and get the old data.
                      */
                      INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[Tr ID],[RowLog Contents 0_var]) 
                      SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],@TransactionID AS [Tr ID]
                      ,REPLACE (UPPER([RowLog Contents 0_var]),UPPER(CAST('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents1") )', 'varchar(max)')),UPPER(cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents0") )', 'varchar(max)'))) AS [RowLog Contents 0_var]
                      FROM  @ModifiedRawData WHERE [PAGE ID]=@ConsolidatedPageID And [Slot ID]=@Slotid And [AllocUnitId]=@AllocUnitID ORDER BY [ID] DESC
 
                      --- Convert the old data which is in string format to hex format.
                      UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
                      FROM @ModifiedRawData WHERE [Slot ID]=@SlotID
 
              END
         IF @Operation ='LOP_MODIFY_COLUMNS'                   
             BEGIN
 
                          /* If it is @Operation Type is 'LOP_MODIFY_ROW' then we need to follow a different procedure to recover modified
                             .Because this time the data is also in chunks but merge with the data log.
                          */
                            --First, we need to get the [RowLog Contents 3] Because in [Log Record] field the modified data is available after the [RowLog Contents 3] data.
                            SET @RowLogContents3_Var=cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents3") )', 'varchar(max)')
                            SET @LogRecord_Var =cast('' AS XML).value('xs:hexBinary(sql:variable("@LogRecord"))', 'varchar(max)')
            
                            DECLARE @RowLogData_Var VARCHAR(Max)
                            DECLARE @RowLogData_Hex VARBINARY(Max)
                            ---First get the modifed data chunks in string format 
                            SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var,@LogRecord_Var) +LEN(@RowLogContents3_Var) ,LEN(@LogRecord_Var))
                            --Then convert it into the hex values.
                            SELECT @RowLogData_Hex=CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@RowLogData_Var"),0) )', 'varbinary(max)')
                            FROM (SELECT CASE SUBSTRING(@RowLogData_Var, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
                            DECLARE @TotalFixedLengthData INT 
                            DECLARE @FixedLength_Offset INT
                            DECLARE @VariableLength_Offset INT
                            DECLARE @VariableLength_Offset_Start INT
                            DECLARE @VariableLengthIncrease INT
                            DECLARE @FixedLengthIncrease INT
                            DECLARE @OldFixedLengthStartPosition INT
                            DECLARE @FixedLength_Loc INT
                            DECLARE @VariableLength_Loc INT
                            DECLARE @FixedOldValues VARBINARY(MAX)
                            DECLARE @FixedNewValues VARBINARY(MAX)
                            DECLARE @VariableOldValues VARBINARY(MAX)
                            DECLARE @VariableNewValues VARBINARY(MAX)
                         
                            -- Before recovering the modfied data we need to get the total fixed length data size and start position of the varaible data
                             
                            SELECT TOP 1 @TotalFixedLengthData=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2)))) 
                            ,@VariableLength_Offset_Start=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))+5+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))
                            FROM @ModifiedRawData
                            ORDER BY [ID] DESC
 
                            SET @FixedLength_Offset= CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(@RowLogContents0))))--)
                            SET @VariableLength_Offset=CONVERT(int,CONVERT(BINARY(2),REVERSE(@RowLogContents0)))
                             
                            /* We already have modified data chunks in @RowLogData_Hex but this data is in merge format (modified plus actual data)
                              So , here we need [Row Log Contents 1] field , because in this field we have the data length both the modified and actual data
                               so this length will help us to break it into original and modified data chunks.
                            */
                            SET @FixedLength_Loc= CONVERT(INT,SUBSTRING(@RowLogContents1,1,1))
                            SET @VariableLength_Loc =CONVERT(INT,SUBSTRING(@RowLogContents1,3,1))
 
                            /*First , we need to break Fix length data actual with the help of data length  */ 
                            SET @OldFixedLengthStartPosition= CHARINDEX(@RowLogContents4,@RowLogData_Hex)
                            SET @FixedOldValues = SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition,@FixedLength_Loc)
                            SET @FixedLengthIncrease = (CASE WHEN (Len(@FixedOldValues)%4)=0 THEN 1 ELSE (4-(LEN(@FixedOldValues)%4))  END)
                            /*After that , we need to break Fix length data modified data with the help of data length  */
                            SET @FixedNewValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease,@FixedLength_Loc) 
 
                            /*Same we need to break the variable data with the help of data length*/
                            SET @VariableOldValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease),@VariableLength_Loc)
                            SET @VariableLengthIncrease =  (CASE WHEN (LEN(@VariableOldValues)%4)=0 THEN 1 ELSE (4-(Len(@VariableOldValues)%4))+1  END) 
                            SET @VariableOldValues =(CASE WHEN @VariableLength_Loc =1 THEN  @VariableOldValues+0x00 ELSE @VariableOldValues END)
                 
                            SET @VariableNewValues =SUBSTRING(SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease-1)+@VariableLength_Loc+@VariableLengthIncrease,Len(@RowLogData_Hex)+1),1,Len(@RowLogData_Hex)+1) --LEN(@VariableOldValues)
                            
                            /*here we need to replace the fixed length &  variable length actaul data with modifed data 
                            */ 
                             
                            SELECT TOP 1 @VariableNewValues=CASE
                            WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)+1),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)+1)
                            WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)),[RowLog Contents 0])<>0 THEN  SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues))
                            WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-1),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-1)--3 --Substring(@VariableNewValues,0,Len(@VariableNewValues)-1)
                            WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-2),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-2)
                            WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-3),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-3) --5--Substring(@VariableNewValues,0,Len(@VariableNewValues)-3)
                            END
                            FROM @ModifiedRawData  WHERE [Slot ID]=@SlotID  ORDER BY [ID] DESC
                                          
                            INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[Tr ID],[RowLog Contents 0_var],[RowLog Contents 0]) 
                            SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],@TransactionID,NULL
                            ,CAST(REPLACE(SUBSTRING([RowLog Contents 0],0,@TotalFixedLengthData+1),@FixedNewValues, @FixedOldValues) AS VARBINARY(max))
                            + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2)
                            + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3, CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)))
                            + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)), 2)
                            + SUBSTRING([RowLog Contents 0],@VariableLength_Offset_Start,(@VariableLength_Offset-(@VariableLength_Offset_Start-1)))
                            + CAST(REPLACE(SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1,LEN(@VariableNewValues))
                            , @VariableNewValues
                            , @VariableOldValues) AS VARBINARY) 
                            + Substring([RowLog Contents 0],@VariableLength_Offset+LEN(@VariableNewValues)+1,LEN([RowLog Contents 0]))
                            FROM @ModifiedRawData  WHERE [Slot ID]=@SlotID  ORDER BY [ID] DESC
                            
             END
 
        FETCH NEXT FROM Page_Data_Cursor INTO   @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
    END
 
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
 
DECLARE @RowLogContents VARBINARY(8000)
Declare @AllocUnitName NVARCHAR(Max)
Declare @SQL NVARCHAR(Max)
 

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
(
    [ID] INT IDENTITY(1,1),
    [RowLogContents]    VARBINARY(8000),
    [AllocUnitID]       BIGINT,
    [Tr ID] sysname			  ,	
    [Transaction ID]    NVARCHAR(Max),
    [Slot ID]           INT,
    [FixedLengthData]   SMALLINT,
    [TotalNoOfCols]     SMALLINT,
    [NullBitMapLength]  SMALLINT,
    [NullBytes]         VARBINARY(8000),
    [TotalNoofVarCols]  SMALLINT,
    [ColumnOffsetArray] VARBINARY(8000),
    [VarColumnStart]    SMALLINT,
    [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] 
 
,[Tr ID]
,[ID] AS [Transaction ID]  
 
,[Slot ID] as [Slot 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 (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols] 
 
--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)
* 2)  ELSE null  END) AS [ColumnOffsetArray] 
 
--  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (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 (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2)) 
 
ELSE null End AS [VarColumnStart]
From @ModifiedRawData
 
),
 
---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]
        ,[Tr ID]
        ,[Transaction ID]
        ,[Slot 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 ',' +
        (CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]
FROM
N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
FROM RowData D
 
CREATE TABLE [#temp_Data]
(
   
    [FieldName]  VARCHAR(MAX) COLLATE database_default NOT NULL,
    [FieldValue] VARCHAR(MAX) COLLATE database_default NULL,
    [Rowlogcontents] VARBINARY(8000),
    [Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL,
    [Tr ID] SYSNAME,
    [Slot ID] INT,
    [NonID] INT
    --[System_type_id] int
)
---Create common table expression and join it with the rowdata table
--to get each column details
;With CTE AS (
/*This part is for variable data columns*/
SELECT
A.[ID],
Rowlogcontents,
[Transaction ID],
[Tr ID],
[Slot ID],
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,
--Calculate the variable column size from the variable column offset array
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],
 
---Calculate the column length
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END) AS [Column Length]
 
--Get the Hexa decimal value from the RowlogContent
--HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])
--This is the data of your column but in the Hexvalue
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END)
- ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) END AS hex_Value
 
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 
A.[ID],
Rowlogcontents,
[Transaction ID],
[Tr ID],
[Slot ID],
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,
(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], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(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
,syscolumns.length) END AS hex_Value
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 )
 
--Converting data from Hexvalue to its orgional datatype.
--Implemented datatype conversion mechanism for each datatype
--Select * from sys.columns Where [object_id]=object_id('' + @SchemaName_n_TableName + '')
--Select * from CTE
 
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),REPLACE(hex_Value, 0x00, 0x20))))  --VARCHAR,CHAR
 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 IN( 40) Then CONVERT(VARCHAR(MAX),CONVERT(DATE,CONVERT(VARBINARY(8000),(hex_Value))),100) --DATE This datatype only works for SQL Server 2008
 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), CAST(CONVERT(NUMERIC(38,30), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- NUMERIC
 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 =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- DECIMAL
 WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), 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 =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
 END AS FieldValue
,[Rowlogcontents]
,[Transaction ID]
,[Tr ID]
,[Slot ID]
,[ID]
FROM CTE ORDER BY nullbit
 
/*Create Update statement*/
/*Now we have the modified and actual data as well*/
/*We need to create the update statement in case of recovery*/

 
;With CTE AS (SELECT
(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + [A].[FieldValue]+ '''','NULL')+ ' ,'+' '
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='  + ISNULL(+ 'N''' +[A].[FieldValue]+ '''','NULL')+ ' ,'+''
WHEN system_type_id In (58,40,61,36) THEN QUOTENAME([Name]) + '='  + ISNULL(+  ''''+[A].[FieldValue]+ '''','NULL') + '  ,'+' '
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL([A].[FieldValue],'NULL')+ ' ,'+' '
END) as [Field]
,A.[Slot ID]
,A.[Transaction ID] as [Transaction ID]
,A.[tr ID]
,'D' AS [Type] 
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID]  FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] On  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND A.[Fieldname] = D.[name]
WHERE ISNULL([A].[FieldValue],'')<>ISNULL([B].[FieldValue],'')
UNION ALL
 
SELECT(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + [A].[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='+ ISNULL(+ 'N''' +[A].[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (58,40,61,36) THEN QUOTENAME([Name]) + '=' + ISNULL(+  ''''+[A].[FieldValue]+ '''','NULL') + ' AND '+''
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL([A].[FieldValue],'NULL') + ' AND '+''
END) AS [Field]
,A.[Slot ID]
,A.[Transaction ID] AS [Transaction ID]
,A.[tr ID]
,'S' AS [Type]
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] ON  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND [A].[Fieldname]=D.[name]
WHERE ISNULL([A].[FieldValue],'')=ISNULL([B].[FieldValue],'')
AND A.[Transaction ID] NOT IN (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
)
 
,CTEUpdateQuery AS (SELECT 'UPDATE ' +  @SchemaName_n_TableName +  ' SET ' + LEFT(
STUFF((SELECT ' ' + ISNULL([Field],'')+ ' ' FROM CTE B 
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,''), 
 
LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE B 
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,'') )-2)
 
+ '  WHERE  ' +
 
LEFT(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C 
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,'') ,
 
LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C 
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,''))-4)
AS [Update Statement],
[Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID]
FROM CTE A
GROUP BY [Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID] )
 
INSERT INTO #temp_Data 
SELECT 'Update Statement',ISNULL(A.[Update Statement],''),A.[Rowlogcontents],A.[Transaction ID],A.[tr ID],A.[Slot ID],A.[NonID] FROM CTEUpdateQuery A


INSERT INTO #temp_Data 
Select 'Modified User Name',C.[name],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID] from #temp_Data A
Inner Join fn_dblog(NULL,NULL) B On A.[tr ID]= B.[Transaction ID]
And B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='UPDATE'
Inner Join sys.sysusers  C On B.[Transaction SID]=C.[Sid]
Where [FieldName]='Update Statement'
Group By C.[name],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID]
Union ALL
Select 'Modified Date Time',B.[Begin Time],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID] from #temp_Data A
Inner Join fn_dblog(NULL,NULL) B On A.[tr ID]= B.[Transaction ID]
And B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='UPDATE'
Where [FieldName]='Update Statement'
Group By B.[Begin Time],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID]

/**************************/
--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('')
), 1, 1, '')
 
Print @FieldName
--Finally did pivot table and got the data back in the same format.
--The [Update Statement] column will give you the query that you can execute in case of recovery.
SET @sql = 'SELECT ' + @FieldName  + ',[Update Statement],[Modified User Name],[Modified Date Time] FROM #temp_Data 
PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ',[Update Statement],[Modified User Name],[Modified Date Time])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])'
Print @sql
EXEC sp_executesql @sql
 
GO
--Execute the procedure like
--Recover_Modified_Data_Proc 'Database name''Schema.table name','Date from' ,'Date to'
 
----EXAMPLE #1 : FOR ALL MODIFIED RECORDS
EXEC Recover_Modified_Data_Proc 'test','dbo.Student'
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Modified_Data_Proc  'test','dbo.Student','2000/01/01','9999/12/31'
--It will give you the result of all modified records.
--OUTPUT

Who modified at what time 1.3

Read Full Post »

A few days ago I was working on indexes on a development server.  Accidentally I dropped few indexes. Fortunately, since I normally take the database backup, I restored the backup and it recovered all indexes perfectly. What if I didn’t have the backup and I did it on production server, it would be just  BOOOOOM ;). However, I looked for a solution on the internet how to recover the dropped index without backup. But could not get the proper solution. So finally I decided to develop a solution which I would be sharing with you.

Note : It is recommended to take database backup on regular basis and recover any disaster from backup.

Lets create & drop one nonclustered index on a table to demonstrate it.

USE AdventureWorks2012
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample]
ON [Purchasing].[PurchaseOrderDetail]
(
[OrderQty],
[ReceivedQty],
[RejectedQty],
[ProductID]
)
GO
DROP INDEX [IX_PurchaseOrderDetail_Sample]
ON [Purchasing].[PurchaseOrderDetail]

Lets recover it step by step :

Step 1:
In this step, you need to create the given below function in the database from where you need to do the recovery.

USE AdventureWorks2012
GO
CREATE FUNCTION [dbo].[UDF_Get_Index_Column_Name]
(@Object_id bigint,@Column_id bigint)
RETURNS varchar(Max)
AS
Begin
          Declare @Column_Name as varchar(Max)
	  Select @Column_Name=[name] from sys.columns
          where object_id =@Object_id And column_id =@Column_id
	  Return @Column_Name
End
GO

Step 2:
In this step, you need to create the given below stored procedure in the database from where you need to do the recovery.

CREATE PROCEDURE Recover_Dropped_Index_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

;With CTE AS (Select
Row_number() Over(Partition By A.[Transaction ID]
Order By [Transaction ID]) as [SNO]
,A.[Transaction ID]
,'CREATE ' +
(Case When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =0
Then 'Heap'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =1
Then 'Clustered'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =2
Then 'Nonclustered'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =3
Then 'XML'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =4
Then 'Spatial'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =6
Then 'Nonclustered columnstore'
end)
+ ' INDEX ' + Quotename(
Replace(Substring(
A.[RowLog Contents 0],47,LEN(A.[RowLog Contents 0])),0x00,0x))
+' ON ' + Sch.name +'.'
+ Object_Name(
Convert(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))
as [Command1]

, [dbo].[UDF_Get_Index_Column_Name] (
  Convert(bigint,
  Convert(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))
, Convert(bigint
, Convert(varbinary,Substring(A.[RowLog Contents 0],20,2))))
  as [Column Name]

, Convert(int,Substring(A.[RowLog Contents 0],22,1)) as [Type]

, SUM(1) OVER(Partition By A.[Transaction ID]) as [Total Rows]

From sys.fn_dblog(NULL, NULL) As A
Inner Join sys.tables As tbl
On Object_Name(Convert
(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))=tbl.name
Inner Join sys.schemas As Sch On tbl.schema_id =sch.schema_id
Where A.AllocUnitName In ('sys.sysiscols.clst','sys.sysidxstats.clst')
And Context In ('LCX_MARK_AS_GHOST', 'LCX_HEAP')
And Operation in ('LOP_DELETE_ROWS')
And A.[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] Like '%DROP INDEX%'
And  CONVERT(NVARCHAR(11),[Begin Time]) Between @Date_From And @Date_To)
)
Select (Case When [type]=0 And [SNo] =2 And [SNO]<>[Total rows]
Then '(' + [Column Name] +','
When [type]=0 And [SNo] =2 And [SNO]=[Total rows]
Then '(' + [Column Name] +')'
When [type]=0 And [SNo] >2 And [SNO]<>[Total rows]
Then  [Column Name] + ','
When [type]=0 And [SNo] >2 And [SNO]=[Total rows]
Then  [Column Name] + ')'
else Command1 end) AS [Text] from CTE
GO

Step 3 :
In this step, you need to execute the above stored procedure to recover the dropped index. Given below is the syntax.
You must supply the correct recovery dates to the stored procedure to recover proper records.

USE AdventureWorks2012
GO
EXEC [Recover_Dropped_Index_Proc] '2013/04/01','9999/12/31'
--OUTPUT

recover_dropped_index1.1

Step 4 :
Just copy the above result set and paste in the query window and execute it using correct database and you will get your indexes back in your database.

Read Full Post »

sys.dm_exec_query_stats is very helpful dynamic management view when it comes to query recovery.
Sometimes we write queries to perform some operations in the database and after that close the script window without saving. Later on realize, if only had I saved it, it would have saved some time. Here the solution comes namely sys.dm_exec_query_stats.
This view can recover your query from sql server. Let me create an example to explain it.

First open a new query window and execute the given below script and close the query without saving it.

USE AdventureWorks2012
GO
Select * from Sales.vStoreWithContacts
Select * from Production.ScrapReason
Select * from Sales.vStoreWithAddresses
Select * from Purchasing.vVendorWithContacts
Select * from HumanResources.Shift
Select * from Purchasing.vVendorWithAddresses
Select * from Production.ProductCategory
Select * from Purchasing.ShipMethod
Select * from Production.ProductCostHistory
Select * from Production.ProductDescription
Select * from Sales.ShoppingCartItem

After that, just execute the given below script, it will recover your query.

SELECT Distinct qt.TEXT AS [Queries]
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 

dm_exec_query_stats1.1

The reason why we used distinct in the above query is, there are queries that we have executed multiple times that will appear as well.

Still, I am searching for a solution that can link these queries to the user who executed it so the recovery would be easier. Will update you on this in the upcoming post.

Reference : MSDN

Read Full Post »

In my first article, I wrote about “How to recover the deleted data from SQL Server” from SQL server log. After this article, I received a lot of queries to recover dropped objects like tables, stored procedure, functions & triggers with the help of SQL server Log.

In this article, we will learn how to recover views, stored procedures, functions & triggers via SQL server log.

Step 1 :
Lets create few objects to explain the recovery process.

CREATE TABLE [dbo].[Student](
      [Sno] [int] NOT NULL,
      [Student ID] nvarchar(6) Not NULL ,
      [Student name] [varchar](50) NOT NULL,
      [Date of Birth]  datetime not null,
      [Weight] [int] NULL)

GO
Create View Vw_Student
as
Select * from [Student]
GO
Create Procedure SP_Student
@StudentID nvarchar(6)
as
Select * from Student Where [Student ID] =@StudentID
GO
Create FUNCTION [dbo].[Fn_Student](@StudentID nvarchar(6))
RETURNS int
AS
Begin
	Declare @Weight int
	Select  @Weight = [Weight]
        from Student Where [Student ID] =@StudentID
	Return 	@Weight
End
GO
CREATE TRIGGER trg_Student
ON Student
FOR INSERT
AS RAISERROR (50009, 16, 10)
GO

Step 2:
Lets drop these objects.

Drop View [dbo].[Vw_Student]
GO
Drop Procedure [dbo].SP_Student
GO
Drop Function [dbo].[Fn_Student]
GO
Drop Trigger [dbo].[trg_Student]
GO

Step 3:
Check the existence of these objects to make sure that objects are dropped properly.

Select * from [Vw_Student]
GO
EXEC SP_Student 1
GO
Select dbo.[Fn_Student](1)

Step 4:
Create the given below stored procedure to recover the dropped objects.


-- Script Name: Recover_Dropped_Objects_Proc
-- Script Type : Recovery Procedure
-- Develop By: Muhammad Imran
-- Date Created: 04 Dec 2012
-- Modify Date:
-- Version    : 1.0

Create PROCEDURE Recover_Dropped_Objects_Proc
@Database_Name NVARCHAR(MAX),
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

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

Select Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
from fn_dblog(NULL,NULL)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
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]='DROPOBJ'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0
GO

--Execute the procedure like
--EXEC Recover_Dropped_Data_Proc 'Database Name'

----EXAMPLE #1 : FOR ALL Dropped Objects
EXEC Recover_Dropped_Objects_Proc 'test'
--GO
------EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Dropped_Objects_Proc 'test','2011/12/01','2013/01/30'
--RESULT

Read Full Post »

Older Posts »