Feeds:
Posts
Comments

Archive for April, 2012

How sequence works in SQL Server 2012

Posted in Solutions, SQL SERVER, tagged , on April 29, 2012| 13 Comments »

Microsoft launched many new features in the SQL server 2012 and one of its best features is SEQUENCE Object.
Let me explain it with a real world example:

It generates a sequence (Auto number) without associating with the table globally. (In earlier versions, we had to generate sequence manually).
The sequence should be of numeric data type (tinyint, smallint, int, bigint, decimal and numeric (scale should be zero)) and it can be generated in an ascending or descending order at a particular interval and we can also define the (cycle) to restart the sequence.

SYNTAX :
CREATE SEQUENCE [schema_name].sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]
But, the question is if we have an identity property, then why do we need sequence? Given below is an example that will demonstrate the importance of sequence object.
Example :
We developed a ticketing system in one of the service industries and the requirement was that we had to create tickets for multiple services.
And the ticket should not be repeated in any service. And once we reach 1000 tickets, we need to restart it from ‘1’.
But, we were maintaining multiple service data in multiple tables. So we need to maintain a global unique auto-number and here it comes to Sequence.

Lets proceed now step by step.

Step 1 :
First, you need to create a sequence object namely “Ticket”

CREATE SEQUENCE [dbo].[Ticket]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE
CACHE
GO

Step 2 :
You need to create two tables to maintain different types of services in different tables.

Create table tbl_Renewal_Application(
[TicketNo] INT NOT NULL,
[App Date] datetime,
[Comments] varchar(max)

Create table tbl_New_Application(
[TicketNo] INT NOT NULL,
[App Date] datetime,
[Comments] varchar(max))

Step 3:
Now, you need to insert the [Ticket No] (Auto Increment) in different tables without duplication and should be consistent.

INSERT tbl_Renewal_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket], Getdate(),'Renewal-1')
GO
INSERT tbl_New_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'New App-1')
GO
INSERT tbl_Renewal_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'Renewal-2')
GO
INSERT tbl_New_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'New App-2')

Now, if you browse the data from both tables, you can see the sequence number (Auto Number) available in the same sequence as we inserted into the table.
Here is the result :

Select * from tbl_Renewal_Application
Select * from tbl_New_Application


The beauty of this object is, if you need a new counter, you need not insert value in the table and then get the counter. By simply executing one query you can get the next counter.
Here is the query.

SELECT NEXT VALUE FOR dbo.[ticket]

–Result is 5
Note : At the time of implementation, if you need to start your first counter from 500 then, you need to set the start property of sequence to 500.

Read Full Post »

How to create Bulk SQL Users

Posted in Solutions, SQL SERVER, tagged , , on April 24, 2012| 3 Comments »

Sometimes, we have a request to create few hundred users in sql server specially at the time of new deployment.
The first solution is we can create users one by one, but it takes some time to create in the SQL Server.
The second solution is using Microsoft Excel. In this case, we need to enter all the users, its password and related information in excel sheet, and using this script we can create the sql script from this excel sheet.

To create SQL Authentication users in SQL, please enter your data in this excel sheet format (kindly do not change the header name) :
Download excel sheet for SQL.

To create Windows Authentication users in SQL, please enter your data in this excel sheet format (kindly do not change the header name) :
Download excel sheet for Windows.

Given below is the script with examples:

CREATE PROCEDURE CREATE_MULTIPLE_LOGIN_PROC
@Excel_File_Name_Path VARCHAR(MAX),
@Excel_Sheet_Name VARCHAR(MAX),
@Authentication_Type VARCHAR(MAX)
AS

DECLARE @SQL VARCHAR(MAX)

SET @SQL=''
SET @Excel_Sheet_Name =QUOTENAME (@Excel_Sheet_Name + '$' )--Add "$" to the excel sheet name to make it accessable.
SET @Excel_File_Name_Path='''Excel 4.0;Database=' + @Excel_File_Name_Path + ''

CREATE TABLE #temp_Data
(
[SNO]	 INT,
[SCRIPT] NVARCHAR(MAX)
)
IF @Authentication_Type ='SQL' -- If Authentication Type is SQL
Begin
SET @SQL = 'INSERT INTO #temp_Data ' + 'SELECT  [SNO],'+ '''CREATE LOGIN ''
+ QUOTENAME([LOGIN NAME])
+ '' WITH PASSWORD=N''''''+ [PASSWORD] + ''''''
,CHECK_EXPIRATION=''+[CHECK_EXPIRATION]+ ''
,CHECK_POLICY=''+[CHECK_POLICY]+ ''
,DEFAULT_DATABASE=''+QUOTENAME([DEFAULT_DATABASE])+ ''
,DEFAULT_LANGUAGE=''+QUOTENAME([DEFAULT_LANGUAGE])+ '''''
+ ' AS [DATA] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ' + @Excel_File_Name_Path + ''', ' +
'''SELECT * FROM '+@Excel_Sheet_Name + ''')'
Print @SQL
EXEC(@SQL)

SET @SQL = ''
SET @SQL = 'INSERT INTO #temp_Data Select [SNO]
,''EXEC sys.sp_addsrvrolemember @loginame =N''''''+ [LOGIN NAME] + ''''''
, @rolename =N''''''+[FieldValue] + ''''''''
FROM (
Select [SNO],
[LOGIN NAME],
CONVERT(NVARCHAR(Max),[SERVER ROLE 1]) as [SERVER ROLE 1]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 2]) as [SERVER ROLE 2]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 3]) as [SERVER ROLE 3]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 4]) as [SERVER ROLE 4]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 5]) as [SERVER ROLE 5]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 6]) as [SERVER ROLE 6]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 7]) as [SERVER ROLE 7]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 8]) as [SERVER ROLE 8]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 9]) as [SERVER ROLE 9]  ,
CONVERT(NVARCHAR(Max),[SERVER ROLE 10]) as [SERVER ROLE 10]
FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ' + @Excel_File_Name_Path + ''', ' +
'''SELECT * FROM '+@Excel_Sheet_Name + '''))Main
UNPIVOT(FieldValue  FOR FieldName IN (
[SERVER ROLE 1] ,
[SERVER ROLE 2] ,
[SERVER ROLE 3] ,
[SERVER ROLE 4] ,
[SERVER ROLE 5] ,
[SERVER ROLE 6] ,
[SERVER ROLE 7] ,
[SERVER ROLE 8] ,
[SERVER ROLE 9] ,
[SERVER ROLE 10]
)
)Sup'
Print @SQL
EXEC(@SQL)
END
ELSE IF @Authentication_Type ='Windows'
BEGIN
---Create
SET @SQL = 'INSERT INTO #temp_Data ' + 'SELECT  [SNO],'+ '''CREATE LOGIN ''
+ QUOTENAME([LOGIN NAME]) + ''
FROM WINDOWS WITH DEFAULT_DATABASE=''+QUOTENAME([DEFAULT_DATABASE])+ ''
, DEFAULT_LANGUAGE=''+QUOTENAME([DEFAULT_LANGUAGE])+ '''''
+ ' AS [DATA] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
' + @Excel_File_Name_Path + ''', ' +
'''SELECT * FROM '+@Excel_Sheet_Name + ''')'
Print @SQL
EXEC(@SQL)
SET @SQL = ''
END

Select [SCRIPT] from #temp_Data Order By [SNO],[SCRIPT]
GO
--Syntax
--CREATE_MULTIPLE_LOGIN_PROC 'Excel Sheet Path','Sheet Name (By Default it is sheet1)','Authentication Type'

--Example 1 :
CREATE_MULTIPLE_LOGIN_PROC 'E:\login_list-for-sql.xls','Sheet1','SQL' -- To Create SQL users
GO
--Result


--Example 2 :
CREATE_MULTIPLE_LOGIN_PROC 'E:\login_list-for-Windows.xls','Sheet1','Windows' -- To Create Windows users
--Result

I welcome your valuable feedback/input.

Read Full Post »

How to convert Iranian Calendar to Gregorian Calendar & vice versa.

Posted in Solutions, SQL SERVER, tagged , , , , , , on April 18, 2012| 5 Comments »

Here is a solution to convert Iranian Calendar (also known as Persian calendar or the Jalaali Calendar) to Gregorian Calendar & vise versa in SQL server.
To develop this solution, we used Julian calendar as an intermediary to convert it & vise versa.

Here is the source code to convert Iranian Calendar to Gregorian Calendar with example:

--First twe need to convert Persian calendar date to Julian Calendar date
Create FUNCTION [dbo].[UDF_Persian_To_Julian](@iYear int,@iMonth int,@iDay int)
RETURNS bigint
AS
Begin

Declare @PERSIAN_EPOCH  as int
Declare @epbase as bigint
Declare @epyear as bigint
Declare @mdays as bigint
Declare @Jofst  as Numeric(18,2)
Declare @jdn bigint

Set @PERSIAN_EPOCH=1948321
Set @Jofst=2415020.5

If @iYear>=0
Begin
Set @epbase=@iyear-474
End
Else
Begin
Set @epbase = @iYear - 473
End
set @epyear=474 + (@epbase%2820)
If @iMonth<=7
Begin
Set @mdays=(Convert(bigint,(@iMonth) - 1) * 31)
End
Else
Begin
Set @mdays=(Convert(bigint,(@iMonth) - 1) * 30+6)
End
Set @jdn =Convert(int,@iday) + @mdays+ Cast(((@epyear * 682) - 110) / 2816 as int)  + (@epyear - 1) * 365 + Cast(@epbase / 2820 as int) * 1029983 + (@PERSIAN_EPOCH - 1)
RETURN @jdn
End
Go
--Secondly, convert Julian calendar date to Gregorian to achieve the target.
Create FUNCTION [dbo].[UDF_Julian_To_Gregorian] (@jdn bigint)
Returns nvarchar(11)
as
Begin
Declare @Jofst  as Numeric(18,2)
Set @Jofst=2415020.5
Return Convert(nvarchar(11),Convert(datetime,(@jdn- @Jofst),113),110)
End
Go
-- Here is the example
Select dbo.[UDF_Julian_To_Gregorian](dbo.[UDF_Persian_To_Julian](1391,1,30))
--Result is 04-18-2012

Here is the source code to convert Gregorian Calendar to Iranian Calendar with example:

Create Function dbo.[UDF_Gregorian_To_Persian] (@date datetime)
Returns nvarchar(50)
as
Begin
Declare @depoch as bigint
Declare @cycle  as bigint
Declare @cyear  as bigint
Declare @ycycle as bigint
Declare @aux1 as bigint
Declare @aux2 as bigint
Declare @yday as bigint
Declare @Jofst  as Numeric(18,2)
Declare @jdn bigint

Declare @iYear   As Integer
Declare @iMonth  As Integer
Declare @iDay    As Integer

Set @Jofst=2415020.5
Set @jdn=Round(Cast(@date as int)+ @Jofst,0)

Set @depoch = @jdn - [dbo].[UDF_Persian_To_Julian](475, 1, 1)
Set @cycle = Cast(@depoch / 1029983 as int)
Set @cyear = @depoch%1029983

If @cyear = 1029982
Begin
Set @ycycle = 2820
End
Else
Begin
Set @aux1 = Cast(@cyear / 366 as int)
Set @aux2 = @cyear%366
Set @ycycle = Cast(((2134 * @aux1) + (2816 * @aux2) + 2815) / 1028522 as int) + @aux1 + 1
End

Set @iYear = @ycycle + (2820 * @cycle) + 474

If @iYear <= 0
Begin
Set @iYear = @iYear - 1
End
Set @yday = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, 1, 1)) + 1
If @yday <= 186
Begin
Set @iMonth = CEILING(Convert(Numeric(18,4),@yday) / 31)
End
Else
Begin
Set @iMonth = CEILING((Convert(Numeric(18,4),@yday) - 6) / 30)
End
Set @iDay = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, @iMonth, 1)) + 1

Return Convert(nvarchar(50),@iDay) + '-' +   Convert(nvarchar(50),@iMonth) +'-' + Convert(nvarchar(50),@iYear)
End
GO
-- Here is the example
Select dbo.[UDF_Gregorian_To_Persian]('2012-04-18') as [Persian Date]
--Result is 30-1-1391

Read Full Post »

How to Change the Default SQL Server backup folder

Posted in Solutions, SQL SERVER, tagged , on April 17, 2012| Leave a Comment »

By default, SQL Server takes the backup in the following location (‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\’). But if you don’t take the backup in this location, each time you need to change the backup path.To avoid this, you can set your default database backup path.

Here is the solution :
You can change the default backup path from the given below script.

DECLARE @HkeyLocal nvarchar(18)
DECLARE @BackupDirectory nvarchar(512)
DECLARE @InstanceRegPath sysname
DECLARE @MSSqlServerRegPath nvarchar(31)
DECLARE @NewPath nvarchar(100)

SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SET @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

EXEC xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
SELECT @BackupDirectory -- Read the default backup directory path

-- Update/Change the default backup directory path to @NewPath
SET @NewPath='D:\DBBackup' -- Change the new default backup folder here
EXEC xp_instance_regwrite @HkeyLocal,@InstanceRegPath, N'BackupDirectory', REG_SZ,@NewPath

Here is screen image of before processing this script :

Here is the screen image after processing this script :

Your feedback is very much appreciated. Please do write.

Read Full Post »

How to recover truncated data from SQL Server without Backup

Posted in Solutions, SQL SERVER, tagged , , , , on April 8, 2012| 52 Comments »

I was working on “How to recover truncated data from SQL Server” from last couple of weeks and finally I was successful.

Now, it is very easy to recover truncated data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation.Also, your database recovery model should be FULL).

  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • char
  • varchar
  • nchar
  • nvarchar
  • datetime
  • smalldatetime
  • money
  • smallmoney
  • decimal
  • numeric
  • real
  • float
  • binary
  • varbinary
  • uniqueidentifier

Let me explain this issue demonstrating simple example.

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

--Insert 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)
--Check the existence of the data
Select * from dbo.[Student]


--truncate data
Truncate Table dbo.Student
--Verify the data has been truncated
Select * from dbo.[Student]

Now, you need to create this procedure to recover your truncated data

Create PROCEDURE Recover_Truncated_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 @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT

DECLARE @ConsolidatedPageID VARCHAR(MAX)
Declare @AllocUnitID as bigint
Declare @TransactionID as 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,
[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 LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
,[Slot ID],[AllocUnitId]
FROM    sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')
AND Description Like '%Deallocated%'
/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
AND [Transaction Name]='TRUNCATE TABLE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/

GROUP BY [Description],[Slot ID],[AllocUnitId]
ORDER BY [Slot ID]

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID

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 truncate) from the page

INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');
---Check if any index page is there
If (Select Count(*) From @temppagedata Where [Field]='Record Type' And [Value]='INDEX_RECORD')=0
Begin
DELETE @temppagedata
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;');
End
Else
Begin
DELETE @temppagedata
End

INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID
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,
[RowLog Contents 0_var] VARCHAR(MAX),
[RowLog Contents 0] VARBINARY(8000)
)
--The truncated 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]
,[RowLog Contents 0_var])
SELECT [Page ID],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) ) as [Slot ID]
,[AllocUnitId]
,(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And
[Object] Like '%Memory Dump%'
FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]
From @pagedata B
Where [Object] Like '%Memory Dump%'
Group By [Page ID],[ParentObject],[AllocUnitId]
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

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
(
[RowLogContents]	VARBINARY(8000),
[AllocUnitID]		BIGINT,
[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]

,[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]
,[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 NOT NULL,
[Rowlogcontents] VARBINARY(8000),
[Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL,
[Slot 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 Rowlogcontents,
[Transaction 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  Rowlogcontents,
[Transaction 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 + '')

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 =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 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]
,[Slot ID]
FROM CTE ORDER BY nullbit

--Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)
SET @FieldName = STUFF(
(
SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')

FOR XML PATH('')
), 1, 1, '')

--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  + ' FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ')) AS pvt
ORDER BY Convert(int,[Transaction ID],Convert(int,[Slot ID]))'

EXEC sp_executesql @sql

GO
--Execute the procedure like
--Recover_Truncated_Data_Proc 'Database name''Schema.table name','Date from' ,'Date to'

--EXAMPLE #1 : FOR ALL TRUNCATED RECORDS
EXEC Recover_Truncated_Data_Proc 'testcs','dbo.Student'
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Truncated_Data_Proc  'testcs','dbo.Student','2000/01/01','2012/04/08'
--It will give you the result of all Truncated records.

Explanation

Let’s go through it step by step. The process requires few easy steps:

Step-1:

The first step is to pick the truncated records using sys.fn_dblog(SQL Log)..Given below is the sample query.

SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
,[Slot ID],[AllocUnitId]
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('' + 'dbo.Student' + ''))
AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')
AND Description Like '%Deallocated%'

Step-2:

In the above sample query, the [Page ID] contains file ID and Page ID in a merge and hex format e.g ‘0001:0000005e’, where the actual data resides after truncate.So in this step we need to separate File ID and page ID than convert it into integer and enter these values in DBCC Page to get the truncated data page.

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)
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata

Step-3:

Now, we have truncated data in @pagedata table but in hex values and it contains the entire page data, most of the data we don’t need. So, we will filter our required data via this query. In this query we filter all the records containing ‘Memory Dump ‘in [object] column. But the issue is, we need the data from [value] column and in this column single row data is in multiple rows like.

00000000: 30001400 01000000 00000000 60940000 28000000 †0………..`…(…
00000014: 0500e002 0029002d 00530054 00440030 00300031 †…..).-.S.T.D.0.0.1
00000028: 00426f62 31†††††††††††††††††††††††††††††††††††.Bob1

But, we need to convert it like this in single row.
30001400010000000000000060940000280000000500e0020029002d00
530054004400300030003100426f6231


INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId]
,[RowLog Contents 0_var])
SELECT [Page ID],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) ) as [Slot ID]
,[AllocUnitId]
,(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
--REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,48),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And
[Object] Like '%Memory Dump%'
FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]
From @pagedata B
Where [Object] Like '%Memory Dump%'
Group By [Page ID],[ParentObject],[AllocUnitId]
Order By [Slot ID]

Now, we have each row record in hex format, so the rest of the step we will follow the same as in How to recover deleted data from SQl server to convert it into actual data.

Appreciate your feedback on my posts. Please do comment.

Read Full Post »