I work on sql server 2014 I face issue error as Invalid length parameter passed to the LEFT or SUBSTRING function when run script below ?
Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3
I create table student with rows values insert as below :
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)
2- after create table student and insert rows on it
I make the following
truncate table [dbo].[Student]
after that i need to get truncated data so
I run script below
I get error
Msg 537, Level 16, State 3, Line 96
Invalid length parameter passed to the LEFT or SUBSTRING function.
issue exist on line 96
SELECT [Page ID],
Substring([ParentObject], case when CHARINDEX('Slot', [ParentObject]) < 0 then len([ParentObject]) else ABS(CHARINDEX('Slot', [ParentObject])+4) end, CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) as [Slot ID]
and this script give me error above
so How to solve issue ?
declare @Database_Name NVARCHAR(MAX)='Nahdy'
declare @SchemaName_n_TableName NVARCHAR(MAX)='dbo.homo'
declare @Date_From datetime='1900/01/01'
declare @Date_To datetime ='9999/12/31'
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;');
Begin
DELETE @temppagedata
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
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]
the above script exist on website as below :
https://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/
but it give issue
the goal from script it will return rows truncated data from table students
30001400010000000000000060940000280000000500e0020029002d00
530054004400300030003100426f6231
RESTORE DATABASE db FROM DISK = ...
Since only one table was lost, you probably want to restore it as a copy. Also, you may want to make a point-in-time restore, so that you can recover the table before the accident.
and can you help me by coding to solve issue of substring please
No. To be perfectly blunt. You are attempting an operation which is advanced and well over your head. As a matter of fact, it is advanced for me as well.
It is also very unlikely that you would be able to recover the table this way, because for this to be possible, the following must be true:
The database is in full recovery.
The transaction log has not been backed up since the accident.
This is extremely unlikely, and if would be true, this is a case of mismanagement, because the transaction log should be backed up regularly, several times per day.
Hi @ahmed salah ，
Truncate means removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Since nothing is recorded, the truncated data cannot be recovered.
Some third-party tools seem to be able to recover the data after truncation, but if there is an update, insert or delete operation after truncation, the data page may be overwritten and the data cannot be restored.
Therefore, it is best to back up the data before performing the truncation operation.
If you have any question, please feel free to let me know.
Regards
If the answer is helpful, please click "Accept Answer" and upvote it.
