In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”
Now, it is very easy to recover deleted data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).
- image
- text
- uniqueidentifier
- tinyint
- smallint
- int
- smalldatetime
- real
- money
- datetime
- float
- sql_variant
- ntext
- bit
- decimal
- numeric
- smallmoney
- bigint
- varbinary
- varchar
- binary
- char
- timestamp
- nvarchar
- nchar
- xml
- sysname
Let me explain this issue demonstrating simple example.
--Create Table
Create Table [Test_Table]
(
[Col_image] image,
[Col_text] text,
[Col_uniqueidentifier] uniqueidentifier,
[Col_tinyint] tinyint,
[Col_smallint] smallint,
[Col_int] int,
[Col_smalldatetime] smalldatetime,
[Col_real] real,
[Col_money] money,
[Col_datetime] datetime,
[Col_float] float,
[Col_Int_sql_variant] sql_variant,
[Col_numeric_sql_variant] sql_variant,
[Col_varchar_sql_variant] sql_variant,
[Col_uniqueidentifier_sql_variant] sql_variant,
[Col_Date_sql_variant] sql_variant,
[Col_varbinary_sql_variant] sql_variant,
[Col_ntext] ntext,
[Col_bit] bit,
[Col_decimal] decimal(18,4),
[Col_numeric] numeric(18,4),
[Col_smallmoney] smallmoney,
[Col_bigint] bigint,
[Col_varbinary] varbinary(Max),
[Col_varchar] varchar(Max),
[Col_binary] binary(8),
[Col_char] char,
[Col_timestamp] timestamp,
[Col_nvarchar] nvarchar(Max),
[Col_nchar] nchar,
[Col_xml] xml,
[Col_sysname] sysname
)
GO
--Insert data into it
INSERT INTO [Test_Table]
([Col_image]
,[Col_text]
,[Col_uniqueidentifier]
,[Col_tinyint]
,[Col_smallint]
,[Col_int]
,[Col_smalldatetime]
,[Col_real]
,[Col_money]
,[Col_datetime]
,[Col_float]
,[Col_Int_sql_variant]
,[Col_numeric_sql_variant]
,[Col_varchar_sql_variant]
,[Col_uniqueidentifier_sql_variant]
,[Col_Date_sql_variant]
,[Col_varbinary_sql_variant]
,[Col_ntext]
,[Col_bit]
,[Col_decimal]
,[Col_numeric]
,[Col_smallmoney]
,[Col_bigint]
,[Col_varbinary]
,[Col_varchar]
,[Col_binary]
,[Col_char]
,[Col_nvarchar]
,[Col_nchar]
,[Col_xml]
,[Col_sysname])
VALUES
(CONVERT(IMAGE,REPLICATE('A',4000))
,REPLICATE('B',8000)
,NEWID()
,10
,20
,3000
,GETDATE()
,4000
,5000
,getdate()+15
,66666.6666
,777777
,88888.8888
,REPLICATE('C',8000)
,newid()
,getdate()+30
,CONVERT(VARBINARY(8000),REPLICATE('D',8000))
,REPLICATE('E',4000)
,1
,99999.9999
,10101.1111
,1100
,123456
,CONVERT(VARBINARY(MAX),REPLICATE('F',8000))
,REPLICATE('G',8000)
,0x4646464
,'H'
,REPLICATE('I',4000)
,'J'
,CONVERT(XML,REPLICATE('K',4000))
,REPLICATE('L',100)
)
GO
--Delete the data
Delete from Test_Table
Go
--Verify the data
Select * from Test_Table
Go
--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'
GO
--Recover the deleted data it with date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table','2012-06-01','2012-06-30'
Now, you need to create the procedure to recover your deleted data
Watch on Youtube : How to recover the deleted Data in SQL Server
Explanation:
How does it work? Let’s go through it step by step. The process requires seven easy steps:
Step-1:
We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But, we need only the selected deleted records from the transaction log. So we included three filters (Context, Operation & AllocUnitName).
- Context (‘LCX_MARK_AS_GHOST’and ‘LCX_HEAP’)
- Operation (‘LOP_DELETE_ROWS’)
- AllocUnitName(‘dbo.Student’) –- Schema + table Name
Here is the code snippet:
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)WHERE AllocUnitName =‘dbo.Student’ AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
This query will return number of columns providing different information, but we only need to select the column “RowLog content o, to get the deleted data.
The Column “RowLog content 0″ will look like this:
“0x300018000100000000000000006B0000564920205900000
00500E001002800426F62206A65727279″
Step-2:
Now,we have deleted data but in Hex values but SQL keeps this data in a specific sequence so we can easily recover it.But before recovering the data we need to understand the format. This format is defined in detail in Kalen Delaney’s SQL Internal’s book.
- 1 Byte : Status Bit A
- 1 Byte : Status Bit B
- 2 Bytes : Fixed length size
- n Bytes : Fixed length data
- 2 Bytes : Total Number of Columns
- n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)
- 2 Bytes : Number of variable-length columns
- n Bytes : Column offset array (2x variable length column)
- n Bytes : Data for variable length columns
So, the Hex data“RowLog content 0″ is equal to:
“Status Bit A + Status Bit B + Fixed length size + Fixed length data + Total Number of Columns + NULL Bitmap + Number of variable-length columns + NULL Bitmap+ Number of variable-length columns + Column offset array + Data for variable length columns.”
Step-3:
Now, we need to break the RowLog Content o (Hex value of our deleted data) into the above defined structure.[Color codes are used for reference only]
- [Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
- [Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
- [Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)
- [Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] )
- Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
- Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )
- Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)
Step-4:
Now, we have the split of data as well,so we can find that which one column value is null or not by using Null Bytes. To achieve this convert Null Bytes (Hex value) into Binary format (As discussed, 1 indicates null for the column and 0 means there is some data).Here in this data, the Null Bitmap values are 00000111.We have only five column in student table (used as sample) and first five value of null bitmap is 00000.It means there is no null values.
Step-5:
Now, we have the primary data split (Step-3) and null values (Step-4) as well. After that we need to use this code snippet to get the column data like column name, column size, precision, scale and most importantly the leaf null bit (to ensure that the column is fixed data (<=-1) or variable data sizes (>=1)) of the table.
Select * 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) 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
And join it with our collected data table (Step-1,2,3,4) on the basis of allocunits.[Allocation_Unit_Id].Till now we know the information about the table and data,so we need to utilize this data to break [RowLog Contents 0] into table column data but in hex value. Here we need to take care as the data is either in fixed column size or in variable column size. .
Step-6:
We collected data in hex value (Step-5) with respect to each column. Now we need to convert the data with respect to its data type defined as [System_type_id]. Each type is having different mechanism
for data conversion.
--NVARCHAR ,NCHAR
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))
--VARCHAR,CHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
--TINY INTEGER
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value))))
--SMALL INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value))))
-- INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value))))
-- BIG INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))
--DATETIME
WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100)
--SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME
--- NUMERIC
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))
--MONEY,SMALLMONEY
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2)
--- DECIMAL
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))
-- BIT
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))
--- FLOAT
WHEN system_type_id = 62 THEN RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value))))
--REAL
When system_type_id =59 THEN Left(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8)
--BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varbinary(max)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varchar(max)')
--UNIQUEIDENTIFIER
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))
Step-7:
Finally we do a pivot table over the data and you will see the result. THE DELETED DATA IS BACK.
Note: This data will only for display. It is not available in your selected table but you can insert this data in your table.
I’d really appreciate your comments on my posts, whether you agree or not, do comment.
SQL Server Portal 
Thank you for posting this. I tried your code (copy and paste) and received an error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘(‘.
Any thoughts?
@Simeon : I tested this source code once again.It is working fine.Please send me sql server version ,compatibilty level details.
Thank you.
Imran bhai
–NVARCHAR ,NCHAR
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))
–VARCHAR,CHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
–TINY INTEGER
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value))))
–SMALL INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value))))
— INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value))))
— BIG INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))
–DATETIME
WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100)
–SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) –SMALL DATETIME
— NUMERIC
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))
–MONEY,SMALLMONEY
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2)
— DECIMAL
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))
— BIT
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))
— FLOAT
WHEN system_type_id = 62 THEN RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value))))
–REAL
–BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_value”))’, ‘varbinary(max)’)) = 0 THEN ‘0x’ ELSE ” END) +cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_value”))’, ‘varchar(max)’)
–UNIQUEIDENTIFIER
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))
error show
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘WHEN’.
Imran Bhai I want to recover my deleted data.
Please help me .Its request.
Please solve this problem
Hi,
Can you please send me SQL server details ?
Thanks
Imran
please give me any mail id, i can send u sql data base.
Your database compatibility level (database >> Properties >> Options >> compatibility level) is SQL server 2000 (80) or SQL Server 7.0 (70). Therefore it gives you this error. It should be sql server 2005 (90) and above.
@Simeon : It is working. and perfect.
@Muhammad : You are legend 🙂
Thanks,
Rohit,
Xpode.com
Wonderful article..do keep writing this kind of stuff.
Thanks
Interesting article! I’ve noticed that this function falls over if the table contains an ntext or text type field. I get an error stating that an invalid value was passed to substring. Is there a way to list out the ntext data as well?
Hi Tim,
Now, You can recover the ntext data also.
Imran
Very interesting article, thanks.
A few minor points and one major one.
1. It would be good if you provided the sql script as a separate download.
2. It needs adjustments if run on a CS collation.
3. You seem to be missing the declaration of the variable @sql in the posted code.
The major issue is that even after making all these corrections I am unable to test the posted code on SQL Server 2008 SP3. I don’t think it is your fault though. There seems to be a bug in SQL Server.
I have narrowed it down to the following test case that hangs indefinitely for me and will likely report it on Microsoft Connect shortly.
CREATE TABLE #hex(
[hex_Value] [varbinary](max) NULL
)
INSERT INTO #hex
SELECT 0x00000000006B0000
SELECT LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
FROM #hex
DROP TABLE #hex
Now, the script is compitable with the CS collation and snapshot isolation as well.
I also tested this script in SQL server 2008 SP3 (version detail Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (Intel X86)
Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition
on Windows NT 5.1 (Build 2600: Service Pack 3)) and it is working well.
Thank you
Just to add an addition to the above I don’t get the issue with varbinary(8000). Seems to just be a problem with the MAX datatype.
I have reported the issue to Microsoft Connect here https://connect.microsoft.com/SQLServer/feedback/details/708179/indefinite-hang-with-replace-statement-on-varbinary-max
I notice that when ran in a database with snapshot isolation enabled it does not return the correct values for variable length columns. What license is this code under? I’d like to extend it to handle more cases.
Thank you for your feeback. Your suggestions are very good. I will incorporate your major and minor issues in the script. Am also developing it for other datatypes as well, including snapshot isolation fixes. Please define extention and that will be incorporated as well.
Works great except for MONEY datatypes
E.g.
BidId BidValue CreatedDate LastUpdatedDate LotId UserId WinningBid
3 0.0009 Oct 11 2010 9:49AM Oct 11 2010 9:49AM 47 A970D702-0F55-4D26-8CE6-3E60F27A42D5 0
4 0.0009 Oct 11 2010 9:49AM Oct 11 2010 9:49AM 48 A970D702-0F55-4D26-8CE6-3E60F27A42D5 0
5 0.0009 Oct 11 2010 9:49AM Oct 11 2010 9:49AM 51 A970D702-0F55-4D26-8CE6-3E60F27A42D5 0
Where bidValue is of MONEY datatype always returns 0.0009
Great Work Imran ,Keep doing good.
how much time will it take , after running the EXEC Recover_Deleted_Data_Proc ‘dbo.Student’ , i just tested your sample which you have given above exactly the same way, but at last stage its taking long time , it was running till 1 hour, then i had to close it forcefully… can you please check it..
It normally takes less than 30 second. Kindly send me your SQL server environment along with the version & compatibility level.
Sounds like you are hitting the same issue as I raised in my connect item.
Does the following hang indefinitely for you?
DECLARE @VBMax varbinary(max)= 0x00000000006B0000
SELECT REPLACE(@VBMax, 0x00, 0x20)
I tested this code and it doesn’t hang.
thanks for your post , it was very help ful, one thing noted
your below query
SET @sql = ‘
SELECT ‘ + @FieldName + ‘
FROM #temp_Data
PIVOT
(
Min([FieldValue])
FOR FieldName IN (‘ + @FieldName + ‘)
) AS pvt’
it was giving a syntax error , we changed it to one line like below and it was working fine
SET @sql = ‘SELECT ‘ + @FieldName + ‘FROM #temp_Data PIVOT ( Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘)) AS pvt’
thanks for the post again
Thanks for your positive feedback. I have just fixed it.
It worked when I set varbinary(8000) instead of MAX.
However, it only works on tables with no identities.
If an Identity is set, then it doesn’t return results.
Thank you!
Thanks for your feedback. I have just fixed it.
HI Muhammad,
Could you look into the issue with recovering Money datatype’s as in my previous comment, your script works excellently except for this.
Hi Dany,
Thank you for your feedback. I have fixed this issue and tested with multiple data sets.Given below are few data sets.
Before recovery————After recovery
89.00———————-89.0000
89.1234——————–89.1234
0.0009———————0.0009
0.2009———————0.2009
9999999.2009—————9999999.2009
12345678.9101————–12345678.9101
1000008.901—————-1000008.9010
102.10——————–102.1000
If you are still facing same issue, please post some more sample data.
Thanks very much Muhammad!!
Hello, I need a similar Store procedure but to retrieve modifed data from a table. Have any ideas?
Thanks in advance,
Ivan.
Hi Ivan, Thank you for your request, I am developing this script. Soon it will be available.
Thank you Imran, just let me know when it’s done
Hi Ivan,
Given below is the article to retrieve modified data from a table.
https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
Imran
Great script Muhammad Imran, You saved my day……
I am very much thank full to you
Best Regards
Anil Inampudi
Thanks very much
Could I retrieve the logs given a date range?
Hi Olga, I customized the script , now you can query with any specific date range as well.
Imran
Hi its very useful Imran.. but I tried your code (copy and paste) and received an error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘(‘.
Please check your database compatibility level.Your database compatibility level (database >> Properties >> Options >> compatibility level) is SQL server 2000 (80) or SQL Server 7.0 (70). Therefore it gives you this error. It should be sql server 2005 (90) and above.
Hi Imran,
Thanks for the post, However, it is giving me blank table as the result. I am using sql server 2008. Also, is there a way where we can recover the database lost in update statement i.e. one of the member needs to update some fields in the row and accidentally he updated all other fields to null.
Thanks,
Manoj
Hi Imran,
Just found, it works like charm on the tables created after the stored procedure is created, but not on the earlier created tables.
Any Idea?
Thanks,
Manoj
Hi Manoj,
Thank you for your feedback.Your Database >> Properties >> Options >> Recovery model is simple.
If it is “Full”, you can recover the data at any point of time.
Hi Manoj,
Currently, I am working on how to recover the modified data from SQl server (update statement).Soon it will be available.
Thank you.
Imran
Hi Imran,
Thanks for your reply, The database is set up as full recovery mode, could you please guide, how we can get the information of updated records from the hexadecimal format, currently I am trying to do it on the sample student table and it is not parsing the correct information for the updated record.
Kindly publish when you are done for modified data
Thanks,
Manoj
Hi Manoj,
Given below is the article to recover the modified data.
https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
Imran
H
ey Imran- i have created same table exactly with your script but when I ran the stored procedure I got the error saying that The compatibility level should be equal to or greater SQL SERVER 2005 (90) though I have sql server 2012 ent edition.
As Salam O Alaikum,
Thankyou, excellent article.
Regards,
Atif Raza
Hi Imran,
Looks great but i have to recover the deleted data from sql server 2000 table and there is no backup of it. If you have solution for that it would be really appreciable
Hi Sourabh,
Given below is the solution.
https://raresql.com/2012/01/23/how-to-recover-deleted-data-from-sql-server-2000/
I have tried and tried but it doesn’t work at all…I delete * today and am desperate to find a way to recover my data
Hi Sabine,
You need to execute this query to check that data is available in your log. (Change you table name instead of dbo.student)
Select *
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName Like ‘%dbo.Student%’
if yes, than this procedure can recover your deleted data.
Imran
Hi Imran
When I execute your Procedure I get :-
(8 row(s) affected)
(5 row(s) affected)
Msg 537, Level 16, State 2, Procedure RecoverList, Line 137
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(1 row(s) affected)
I am not sure weather this is the problem but I have to use ‘dbo.candidates.PK_candidates’ as the parameter when I call it, there is no ‘dbo.candidates’ for some reason.
My candidates table is made up of 3 datetime fields and the rest are nvarchars so nothing complicated to process.
Mark
Hi Mark,
This procedure doesnot support text , ntext and few other BLOB data types.And it generates this error because of these data types.Can you please post your table structure and few sample records.
Thank you.
Imran
Hi Imran my data structure is :-
last_name nvarchar(20) Checked
first_name nvarchar(20) Checked
cand_code nvarchar(20) Unchecked
data_orig nvarchar(50) Checked
dob datetime Checked
ni_number nvarchar(10) Checked
sex nvarchar(10) Checked
employer nvarchar(30) Checked
reg_num nvarchar(15) Checked
ethnic nvarchar(60) Checked
empcode nvarchar(20) Checked
locktime datetime Checked
lockedby nvarchar(20) Checked
password nvarchar(10) Checked
email nvarchar(80) Checked
updatedon datetime Checked
Mark
Hi Mark,
I created this table and insert one dummy record.It recovered successfully.I think the issue is with the data.Can you please post some sample data.
Create Table Test
(
last_name nvarchar(20) NULL,
first_name nvarchar(20) NULL,
cand_code nvarchar(20) NOT NULL,
data_orig nvarchar(50) NULL,
dob datetime NULL,
ni_number nvarchar(10) NULL,
sex nvarchar(10) NULL,
employer nvarchar(30) NULL,
reg_num nvarchar(15) NULL,
ethnic nvarchar(60) NULL,
empcode nvarchar(20) NULL,
locktime datetime NULL,
lockedby nvarchar(20) NULL,
password nvarchar(10) NULL,
email nvarchar(80) NULL,
updatedon datetime NULL
)
Insert into test values('last_name','first_name','cand_code','data_orig','1970-12-01','ni_number'
,'male','employer','reg_num','ethnic','empcode',getdate(),'lockedby','password','email',getdate())
Imran
Thanks for the post, However, it is giving me blank table as the result. I am using sql server 2005. Also, while it working fine on the tables created after the stored procedure is created, but not on the earlier created tables.
note that the DB Recovery model is “Full”.
Any Idea?
Thanks,
Tarek Al-Jamal
Hi Tarek,
Thank you for your feedback.There might be multiple reasons not to recover from earlier deleted recods.
1-The transaction log has been truncated or shrunk.
2- There was a bug in this procedure on line #108.
I was filtering the log with the AllocUnitName but if there is a primary key or index in the table
then the AllocUnitName will not be ‘schema.table name’, it will be ‘schema.table name.primary key’. For example : dbo.Student.PK_Student
So, I changed the filter from AllocUnitName to AllocUnitId.
Imran
Dear Imran,
1-The transaction log has not been truncated or shrunk, and when I run
Select *
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName Like ‘%dbo.MyTable%’ I get data.
2- The but in line #108 does not exist in my update.
but it is still is giving me blank table as the result while it working fine on the tables created after the stored procedure is created, but not on the earlier created tables.
please advice
Thanks,
Tarek Al-Jamal
It started to return results when I remove the filter
“And [Transaction Name]=’DELETE'”
I found the [Transaction Name] is NULL for the returned data.
please advice
Hi Tarek,
You are right , in this query the [Transaction name] is NULL,
also there is no indication , when these records has been deleted.
Select *FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName Like '%dbo.student%'.
But, if I need to recover the records deleted today, we cannot.
So What I did , I wrote a separate subquery query to find out all the [Transaction ID] with the particular data range and then pass those transaction IDs to main query to filter it Like
Select *FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName Like '%dbo.student%' And [Transaction ID] In (
Select [Transaction ID]
FROM sys.fn_dblog(NULL, NULL) Where [Transaction name]='DELETE' And CONVERT(NVARCHAR(11),[Begin Time])
Between '2011/12/01' And '2012/01/30' )
One more thing, can you please check the [RowLog Contents 0] field data.Is it starts from 0x30.. or 0x70..?.Because this is your actual data in hex format.
Imran
I did not get your point regarding the queries, but the data is like:
0x30001800630000007000E900E89F0000FC0AE900E89F00000600C40300430043004D005800580058005800580058005800580058005800580058005800580058005800410064006D0069006E00
what do you think the problem is?.
Hi Tarek,
It seems that the format of the data is correct.Please post the table structure (Create table script) as well.
Imran
Hi Mohammad,
below is the table structure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Banks](
[BankNum] [int] NOT NULL,
[BankName] [nvarchar](50) NOT NULL,
[Notes] [nvarchar](250) NULL,
[DateCreated] [datetime] NULL CONSTRAINT [DF_Banks_DateCreated] DEFAULT (getdate()),
[DateModified] [datetime] NULL CONSTRAINT [DF_Banks_DateModified] DEFAULT (getdate()),
[IssuedBy] [nvarchar](30) NULL,
CONSTRAINT [PK_Banks] PRIMARY KEY CLUSTERED
(
[BankNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
but note that this happens to all tables created before the stored procedure not only this one.
Hi Tarek,
Here is your data :
BankNum 99
BankName XXXXXXXXXXXXXXXX
Notes NULL
DateCreated Jan 30 2012 2:08:20:000PM
DateModified Jan 30 2012 2:08:29:000PM
IssuedBy Admin
It seems every thing is fine, But we need to select/print the data on each step in stored procedure to find out which line # script is not working properly.
I will send you a seperate script where all the select and print is enabled , which will help you to rectify problem.
Imran
Dear Mohammad,
thank you for your effort, I am waiting for your script, but I want to tell you that I get the data only when I remove the filter “And [Transaction Name]=’DELETE'”
from the following script
select * FROM sys.fn_dblog(NULL, NULL) –where [Transaction Name] IS NOT 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(‘banks’))AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’) And SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
Where Context IN (‘LCX_NULL’) AND Operation in (‘LOP_BEGIN_XACT’)
And [Transaction Name]=’DELETE’
)
Hi FIrst of all very thanks for this SP. I have tried this in my system. But it retrieved only 293 records, But actually i have deleted 3516 records. Can you please tell why I lost the remaining data..??
Thanks In advance
Hi CntrlZ,
Thank you for your valuable feedback. This stored procedure recovers the deleted data from your active sql server log. If the recovery model is simple or if you restrict the database log size. So it keeps only last transactions according to the defined size.
You can execute given below query to check how many deleted records are there in your active log. Please change the table name in this query from ‘dbo.student’ to your table name.
Select * 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 Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
AND SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
Please let me know,if this query will give you more than 293 rows.
Thank you
Imran
Hi Imran,
This is really good work !!!
I am now in the situation where i have to recover some deleted data. I am tryin to run your script but i am getting
Msg 207, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 195
Invalid column name ‘AllocUnitId’
Thanks
Hi Carol
Thank you for your feedback. I test this code in multiple environments but could not regenerate. Can you please send me your sql server version, service packs & collation details?
I am sending you a test script where select and print statements are there that can help you to trace the bug.
Imran
Very nicely done! I’m glad someone else is also interested in the workings of the transaction log. I had wrote a similar article some time ago, so I’m glad to see a follow-up on modified records.
http://www.sqlservercentral.com/articles/Transaction+Log/71415/
Kraig Kerr
Hi Kraig,
Appreciate your feedback. Hope to write more articles in the near future.
Imran
Hello Imran,
For me its not working
Hi kurakar,
Can you please send some samples (Table structure, insert statement etc.). So, that I can regenerate the bug and fix it.
Also, Please post your sql server environment.
Thank you.
Imran
Salam Imran ,
For me also its not working
Walikum Salam Sajid,
You need to execute the query given below to check whether the data is available in your log or not.
Select [RowLog Contents 0],* FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName Like ‘%dbo.tablename%’
AND Operation in (‘LOP_DELETE_ROWS’).
Also , you need to check your recovery model. It should be FULL.
If the data is available in your log and recovery model is full, then post the script of the table and [RowLog Contents 0] data.
Thank you
Imran
Aslkm, Imran
Is there any easy way 2 recover the deleted data…?
😐
Walikum Salam Nawaz,
Normally, we recover deleted data from the database backup.But if you don’t have data backup.So, you just need to create this stored procedure and recover the data from active log.
Imran
Thanks .its working fine …………..
Great article. Good work here.
Excellent work…
Hi Imran!
It works on a simple table structure. But on tables with existing relations it doesn’t.
Here’s my example table:
TABLE [dbo].[UVPKarticeVozil]
(
[KarticaVozilaID] [int] NOT NULL IDENTITY(1, 1),
[VoziloID] [int] NOT NULL,
[KarticaID] [int] NULL,
[P1] [float] NULL,
[P2] [float] NULL,
[VrstaDogodkaID] [int] NOT NULL,
[FazaDogodkaID] [int] NULL,
[StatusDogodkaID] [int] NOT NULL,
[DatumDogodka] [datetime] NOT NULL,
[DatumVeljavnosti] [datetime] NULL,
[Opis] [nvarchar] (50) COLLATE Slovenian_CI_AS NULL,
[Kolicina] [float] NULL,
[EnotaMereID] [int] NULL,
[Cena] [float] NULL,
[Znesek] [float] NULL,
[Opombe] [varchar] (max) COLLATE Slovenian_CI_AS NULL,
[PartnerID] [int] NULL,
[Aktivna] [char] (1) COLLATE Slovenian_CI_AS NULL,
[PovezanaKarticaID] [int] NULL,
[DokumentUVPID] [int] NULL,
[DodatniPodatki1ID] [int] NULL,
[DodatniPodatki2ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[UVPKarticeVozil] ADD CONSTRAINT [PK_UVPKarticeVozil] PRIMARY KEY CLUSTERED ([KarticaVozilaID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UVPKarticeVozil] ADD CONSTRAINT [FK_UVPKarticeVozil_UVPStatusiDogodkov] FOREIGN KEY ([StatusDogodkaID]) REFERENCES [dbo].[UVPStatusiDogodkov] ([StatusDogodkaID])
GO
ALTER TABLE [dbo].[UVPKarticeVozil] ADD CONSTRAINT [FK_UVPKarticeVozil_UVPVozila] FOREIGN KEY ([VoziloID]) REFERENCES [dbo].[UVPVozila] ([VoziloID])
GO
ALTER TABLE [dbo].[UVPKarticeVozil] ADD CONSTRAINT [FK_UVPKarticeVozil_UVPVrsteDogodkov] FOREIGN KEY ([VrstaDogodkaID]) REFERENCES [dbo].[UVPVrsteDogodkov] ([VrstaDogodkaID])
Maybe you can solve this.
thank you!
Hi
Msg 8115, Level 16, State 6, Procedure Recover_Deleted_Data_Proc, Line 157
Arithmetic overflow error converting varbinary to data type numeric.
The statement has been terminated.
This is Log RowLog Contents 0 data
0x10006703202020343933314F5050454B31313737202020202020202020202020202020203845303030315334303630463031202020202020202020202020202020202020202020394535303033323036303046313120202020202020202020202020202020202020202001E80300000000000020202020202000000000C69F000031323A32333A3436204F5050454B31313737383632343620202020202020202020202020202020202020202021202020202020202020202020202020202020202020202020202020202020202020202020200100000000000000000000000001000000000000000000000000010000000000000000010000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202001000000000000000020202020202020202020202020202020202020202020202020202020202020202020200100000000000000000100000000000000002020010000000000000000010000000000000000000000000180EE3600000000000000000033314F5050454B313137372D4F5050454B313137373836323436202020202020202020202020202020202020202020010000000000000000010000000000000000010000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020200100000000000000000000000001000000000000000000000000010000000000000000000000000100000000000000000000000001000000000000000000000000000000A8D409100822245F0822245F0000003100000000001080FF
Hi Suraj,
Can you please post the structure of the table and sql server environment.
Thank you.
Imran
Hi Imran,
Thanks so much for this!
All my [RowLog Contents 0] start with (0x1000…) and not (0x30..) or (0x70..) Why is that? Is this a problem?
Thanks
Hi,
Can you please post your sql server environment and the table structure.
Hi,
No problem…
SQL environment:
Product: Microsoft SQL Server Developer Edition (64-Bit)
Operating System: Microsoft Windows NT 6.1 (7600)
Platform: NT x64
Version: 10.50.1600.1
Language: English (United StateS)
Memory: 7000 (MB)
Processors: 1
Root Directory: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL
Server Collation: SQL_Latin1_General_CP1_CI_AS
Is Clustered: False
Table structure:
/****** Object: Table [dbo].[Bids] Script Date: 03/21/2012 08:51:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bids](
[BidId] [int] IDENTITY(1,1) NOT NULL,
[BidValue] [money] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
[LotId] [int] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[WinningBid] [bit] NOT NULL,
CONSTRAINT [PK__Bids__4A733D927F60ED59] PRIMARY KEY CLUSTERED
(
[BidId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Bids] WITH NOCHECK ADD CONSTRAINT [Bid_Lot] FOREIGN KEY([LotId])
REFERENCES [dbo].[Lots] ([LotId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Bids] CHECK CONSTRAINT [Bid_Lot]
GO
ALTER TABLE [dbo].[Bids] WITH NOCHECK ADD CONSTRAINT [Bid_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Bids] CHECK CONSTRAINT [Bid_User]
GO
Thanks in advance
Thanks for your posting..
i’ve copied ur code for retreiving deleted records from sql but am getting error like “Msg 229, Level 14, State 5, Procedure Recover_Deleted_Data_Proc, Line 46 SELECT permission denied on object ‘fn_dblog’, a dtabase ‘mssqlsystemresource’, schema ‘sys’.”
Any suggestions
Hi Madhavan,
You donot have sufficient permission to execute “fn_dblog”.
Kindly review your SQL server user permissions/privileges.
Imran
[…] Restoring Deleted Rows – Sql Server […]
Hi Muhammad,
Did you see my reply to your question? I’m actually working on the same recovery as Danny who posted a few times earlier in 2011.
I’m still having the same problem he did where “bidValue is of MONEY datatype always returns 0.0009”.
In order to get your script working I had to remove the filter for [Rowlog Contents 0] beginning with 0x30 or 0x70 as all my rows begin with 0x1000…could this be a clue?
What is also interesting is that the script thinks that there are 9 columns in the table but there are only 7 (TotalNoOfCols) … could this be another clue?
I am busy trying to decode a sample line from [Rowlog Contents 0] using the tutorial from here: http://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/
I suspect that somewhere there is a mismatch in the mapping of the location of the data for BidValue field. What is weird is that all the other fields have mapped correctly and I can see them in the [Rowlog Contents 0] record but not BidValue.
Would it be possible for me to send you the actual record? I would prefer not to post it online as it contains some sensitive information.
Kind regards,
Hi ,
Please send some sample data along with the child table scripts in this email address : mimran18@gmail.com.
Thank you.
Imran
Thank u very much
Great Post
Its works with out any change for me
hi imran,
Really impressive. I found a post similar to yours. but there the datatypes are limited.
So when i starts to fetch internet for data conversion from hex value. i found your post. its quite interesting, you people play with hex data. Can you jus me some book related with converting sql server hex to readable format?
And i got two errors in this script,
1) error: Arithmetic overflow error converting varbinary to data type numeric.
For me this error arises when the final conversion takes place for hex_value to numeric. In the case statment type_id= 108
in the other script the author handles the neric column like this:
WHEN s.system_type_id = 108 AND s.xprec = 5 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(5,2), 0x050200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(6,2), 0x060200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 3 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(6,3), 0x060300 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 7 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(7,2), 0x070200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 8 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(8,2), 0x080200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 9 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(9,2), 0x090200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 10 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(10,2), 0x0A0200 + hex_string))
But he mentioned upto scale 10, what if the scale beyond 10? Am going to search on it. Your suggestion would be highly appreciated.
2) error due to text datatype.
Is it not possible to get back text data from log?
If so you can change the script by eliminating the text column and get the data for other columns rite?
but Now the whole script fails when it found a text column. (Due to conversion into small int)
Hi Shafir,
Thank you so much. I am not sure about the books but many articles and blogs are available on this topic.
Error # 1 : I fixed this issue in the script and here is the description to convert numeric into hex data.
Step 1:
First we need to find out Prec & Scale of the numeric data type and convert Prec & Scale into hex format
In this example :
Prec =38 And Scale =20
So it will be
Select CONVERT(VARBINARY,CONVERT(VARBINARY(1),38)+CONVERT(VARBINARY(1),20)) +CONVERT(VARBINARY(1),0) –=0x261400
Step 2:
Add the actual hex value to the above script so it will be :
Select CONVERT(VARBINARY,CONVERT(VARBINARY(1),38)+CONVERT(VARBINARY(1),20))+CONVERT(VARBINARY(1),0) + 0x0157439756A07628E378C4865AA84C3B4B –= 0x2614000157439756A07628E378C4865AA84C3B4B
Step 3:
Simply cast it into numeric data type.
Select CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),38)+CONVERT(VARBINARY(1),20))+CONVERT(VARBINARY(1),0) + 0x0157439756A07628E378C4865AA84C3B4B) –=999999999999999999.78787575577877889879
Error # 2 :
I am still working on large data types like text,ntext etc.
Will incorporate this feature soon in this script.
Dear Brother,
I am using SQL Server 2008 R2.
I had try your code in simple new created table.
but when I try the same code in my regular table I face an ERROR.
There are number of tables in my database (about 400 to 500) and all the tables contains relationship with each other.
Below I mention my table’s structure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ACCT_MAST](
[SR_NO] [nvarchar](15) NOT NULL,
[CO_CODE] [nvarchar](50) NOT NULL,
[AC_NAME] [nvarchar](150) NULL,
[Ac_ShortName] [nvarchar](150) NULL,
[AC_TYPE] [nvarchar](20) NULL,
[ABC_Code] [nvarchar](50) NULL,
[GR_CODE] [nvarchar](50) NULL,
[RG_Code] [nvarchar](50) NULL,
[BIL_OSOPT] [bit] NULL,
[LDR_DETSUM] [bit] NULL,
[TDS_DEDCT] [bit] NULL,
[COST_CNTR] [bit] NULL,
[REMARK] [nvarchar](50) NULL,
[US_CODE] [int] NULL,
[US_DATE] [datetime] NULL,
[CON_PRSN_One] [nvarchar](50) NULL,
[CON_DESG] [nvarchar](50) NULL,
[ADD1] [nvarchar](500) NULL,
[CITY] [nvarchar](50) NULL,
[PIN] [nvarchar](10) NULL,
[CONTRY] [nvarchar](50) NULL,
[ACC_SHORT] [nvarchar](20) NULL,
[PHONE] [nvarchar](50) NULL,
[PHONE_R] [nvarchar](50) NULL,
[FAX] [nvarchar](50) NULL,
[MOBILE] [nvarchar](50) NULL,
[EMAIL] [nvarchar](50) NULL,
[URL] [nvarchar](50) NULL,
[SYNC_MARK] [nvarchar](2) NULL,
[Title1] [nvarchar](10) NULL,
[PERSON_STATE] [nvarchar](25) NULL,
[Title2] [nvarchar](20) NULL,
[CON_PRSN2] [nvarchar](50) NULL,
[Acc_Profit] [int] NULL,
[FBT_DEDCT] [nvarchar](50) NULL,
[Ack] [nvarchar](50) NULL,
[memo] [varchar](7999) NULL,
[other_details] [varchar](7999) NULL,
[ConMobile1] [nvarchar](50) NULL,
[ConMobile2] [nvarchar](50) NULL,
[CON_DESG2] [nvarchar](50) NULL,
[Discount] [nvarchar](50) NULL,
[Industry_Code] [nvarchar](50) NULL,
[Per1_Email] [nvarchar](50) NULL,
[per2_Email] [nvarchar](50) NULL,
[Greeting_Code] [nvarchar](50) NULL,
[UR_CODE] [nvarchar](50) NULL,
[Autho_Date] [datetime] NULL,
[TEST123] [nvarchar](50) NULL,
[Uniq_no] [int] IDENTITY(1,1) NOT NULL,
[TEMP_AC_NAME] [nvarchar](50) NULL,
[DB_CODE] [nvarchar](50) NULL,
[Otherchrage_Code] [nvarchar](50) NULL,
[Credit_Limit] [int] NULL,
[Total_budget] [float] NULL,
[All_Divison] [bit] NULL,
[Version_No] [nvarchar](50) NULL,
[Desi_code1] [int] NULL,
[Desi_code2] [int] NULL,
[CST_Date] [datetime] NULL,
[GST_Date] [datetime] NULL,
[Cust_Code] [nvarchar](50) NULL,
[PAN_Date] [datetime] NULL,
[TAN_Date] [datetime] NULL,
[Credit_limit1] [nvarchar](50) NULL,
[Block_Account] [bit] NULL,
[EffectiveDate] [datetime] NULL,
[Trade] [nvarchar](50) NULL,
[Cancel_User] [nvarchar](50) NULL,
[Authorize_User] [nvarchar](50) NULL,
[AppointmentDate] [datetime] NULL,
[SurrenderDate] [datetime] NULL,
[Taluka] [nvarchar](50) NULL,
[Distrinct] [nvarchar](50) NULL,
[Credit_Days] [nvarchar](50) NULL,
[Show_Items] [bit] NULL,
[Balance_Inquiry] [bit] NULL,
[statecode] [nvarchar](50) NULL,
[Taluka_code] [nvarchar](50) NULL,
[District_code] [nvarchar](50) NULL,
[ABC_NAME] [nvarchar](500) NULL,
[Description] [nvarchar](500) NULL,
[Industry] [nvarchar](500) NULL,
[From_No] [nvarchar](50) NULL,
[Autho_User] [nvarchar](500) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ACCT_MAST] ADD CONSTRAINT [DF_ACCT_MAST_Acc_Profit] DEFAULT ((0)) FOR [Acc_Profit]
GO
ALTER TABLE [dbo].[ACCT_MAST] ADD CONSTRAINT [DF_ACCT_MAST_All_Divison] DEFAULT ((0)) FOR [All_Divison]
GO
ALTER TABLE [dbo].[ACCT_MAST] ADD CONSTRAINT [DF_ACCT_MAST_Block_Account] DEFAULT ((0)) FOR [Block_Account]
GO
ALTER TABLE [dbo].[ACCT_MAST] ADD CONSTRAINT [DF_ACCT_MAST_Show_Items] DEFAULT ((0)) FOR [Show_Items]
GO
ALTER TABLE [dbo].[ACCT_MAST] ADD CONSTRAINT [DF_ACCT_MAST_Balance_Inquiry] DEFAULT ((0)) FOR [Balance_Inquiry]
GO
When I am trying to recover data from this table I faced the below ERROR.
(8 row(s) affected)
(153 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Deleted_Data_Proc, Line 162
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
(8 row(s) affected)
(153 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Deleted_Data_Proc, Line 162
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
It seems something wrong with data length. Can you please post some sample data so that I can test it. You can use this tool to generate sample data https://raresql.com/2011/12/20/how-to-generate-insert-statements-from-table-data-using-sql-server/
Thank you.
Imran
Dear Brother,
Below is the INSERT To Script of my Table ACCT_MAST:
INSERT INTO [Citizen].[dbo].[ACCT_MAST]
([SR_NO]
,[CO_CODE]
,[AC_NAME]
,[Ac_ShortName]
,[AC_TYPE]
,[ABC_Code]
,[GR_CODE]
,[RG_Code]
,[BIL_OSOPT]
,[LDR_DETSUM]
,[TDS_DEDCT]
,[COST_CNTR]
,[REMARK]
,[US_CODE]
,[US_DATE]
,[CON_PRSN_One]
,[CON_DESG]
,[ADD1]
,[CITY]
,[PIN]
,[CONTRY]
,[ACC_SHORT]
,[PHONE]
,[PHONE_R]
,[FAX]
,[MOBILE]
,[EMAIL]
,[URL]
,[SYNC_MARK]
,[Title1]
,[PERSON_STATE]
,[Title2]
,[CON_PRSN2]
,[Acc_Profit]
,[FBT_DEDCT]
,[Ack]
,[memo]
,[other_details]
,[ConMobile1]
,[ConMobile2]
,[CON_DESG2]
,[Discount]
,[Industry_Code]
,[Per1_Email]
,[per2_Email]
,[Greeting_Code]
,[UR_CODE]
,[Autho_Date]
,[TEST123]
,[TEMP_AC_NAME]
,[DB_CODE]
,[Otherchrage_Code]
,[Credit_Limit]
,[Total_budget]
,[All_Divison]
,[Version_No]
,[Desi_code1]
,[Desi_code2]
,[CST_Date]
,[GST_Date]
,[Cust_Code]
,[PAN_Date]
,[TAN_Date]
,[Credit_limit1]
,[Block_Account]
,[EffectiveDate]
,[Trade]
,[Cancel_User]
,[Authorize_User]
,[AppointmentDate]
,[SurrenderDate]
,[Taluka]
,[Distrinct]
,[Credit_Days]
,[Show_Items]
,[Balance_Inquiry]
,[statecode]
,[Taluka_code]
,[District_code]
,[ABC_NAME]
,[Description]
,[Industry]
,[From_No]
,[Autho_User])
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,)
GO
Waiting for reply sir.
Hi,
Basically, I need the sample data & data is missing in this insert into statement.
Kindly make some sample data and post it, So that I can insert the same data into the table, delete it and then recover it for testing purpose.
Thank you,
Imran
Thank you for your feedback, I found the bug, will fix it and let you know.
Imran
Thanks Brother.
Waiting for solution…
Kindly take a transaction log backup also.
Dear Brother,
I take transaction log backup of the database but how can I send that backup to you.
Regards,
Mayur Makwana
Hi Mayur,
We can utilize this backup at a later stage, once I upload the new solution.
The issue is, SQL server keeps data in [RowLog Contents 0] field in a varbinary data type and its limit is 8000. If you have a bulk data, SQL Server keeps data addresses in [RowLog Contents 0] but not the actual data.It shifts data from [RowLog Contents 0] field to the data pages. Still developing this solution, will upload it soon.
Imran
Hi Mayur,
Solution is done and uploaded.Please test.
https://raresql.com/2012/05/17/how-to-recover-deleted-data-from-sql-server-test/
Thank you.
Imran
Just tested in SQL 2012 and it works great! Thanks for sharing.
Awesome work Imran!!.. This works for some datas, BTW this script not working for following one
CREATE TABLE [dbo].[Student]
(
[Sno] [int] NOT NULL
)
GO
Insert into dbo.[Student] values (1)
Hi Mahadevan,
Thank you for your valuable feedback. I fixed this issue (previously only 0x30,0x70 was handled, now 0x10 is also handled) in this script. Please check.
Imran
Hi Am getting error on execution..
Msg 536, Level 16, State 5, Procedure Recover_Deleted_Data_Proc, Line 162
Invalid length parameter passed to the SUBSTRING function.
SQL server version: 9.0.3042
Hi Vijayanand,
Please post the table script and insert into statement, so that i can test it.
Thank you.
Imran
Hi Imran,
Here it goes…
fyi: It has FK constraints… Dont know if they have any impact.
Table script
/****** Object: Table [dbo].[SubPackageStatus] Script Date: 05/18/2012 17:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubPackageStatus](
[PackageSupplierId] [nvarchar](15) NOT NULL,
[PackageId] [nvarchar](30) NOT NULL,
[SubPackageStatusCodeId] [int] NOT NULL,
[PartNumber] [nvarchar](30) NOT NULL,
[UserId] [nvarchar](12) NOT NULL,
[StatusTime] [datetime] NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
CONSTRAINT [PK_PackageStatus] PRIMARY KEY CLUSTERED
(
[PackageSupplierId] ASC,
[PackageId] ASC,
[SubPackageStatusCodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SubPackageStatus] WITH NOCHECK ADD CONSTRAINT [FK_SubPackage_SubPackageStatus] FOREIGN KEY([PackageSupplierId], [PackageId])
REFERENCES [dbo].[SubPackage] ([SupplierId], [SubPackageId])
GO
ALTER TABLE [dbo].[SubPackageStatus] CHECK CONSTRAINT [FK_SubPackage_SubPackageStatus]
GO
ALTER TABLE [dbo].[SubPackageStatus] WITH NOCHECK ADD CONSTRAINT [FK_SubPackageStatusCode_SubPackageStatus] FOREIGN KEY([SubPackageStatusCodeId])
REFERENCES [dbo].[SubPackageStatusCode] ([SubPackageStatusCodeId])
GO
ALTER TABLE [dbo].[SubPackageStatus] CHECK CONSTRAINT [FK_SubPackageStatusCode_SubPackageStatus]
GO
========================================
Insert script:
INSERT INTO [MACS_GMHSDEV].[dbo].[SubPackageStatus]
([PackageSupplierId]
,[PackageId]
,[SubPackageStatusCodeId]
,[PartNumber]
,[UserId]
,[StatusTime]
,[CreatedBy]
,[CreatedTime]
,[ModifiedBy]
,[ModifiedTime]
,[ObjectVersion])
VALUES
(
,
,
,
,
,
,
,
,
,
,)
GO
Hi Vijay,
I tested it without Foreign Key and it recovered the deleted data.Given below is the script.
INSERT INTO [test].[dbo].[SubPackageStatus]
([PackageSupplierId]
,[PackageId]
,[SubPackageStatusCodeId]
,[PartNumber]
,[UserId]
,[StatusTime]
,[CreatedBy]
,[CreatedTime]
,[ModifiedBy]
,[ModifiedTime]
,[ObjectVersion])
VALUES
(Replicate(‘A’,15)–,
,Replicate(‘B’,30)–
,2–
,Replicate(‘C’,30) —
,Replicate(‘D’,12) —
,Getdate() —
,Replicate(‘E’,12)–
,Getdate()+15 —
,Replicate(‘F’,12) —
,Getdate() +30 —
,3) –)
Select * from dbo.SubPackageStatus
Can you please post the foreign key tables structures so that I can test it.
Thank you.
Imran
Hi Imran,
2 PK tables..
/****** Object: Table [dbo].[SubPackage] Script Date: 05/18/2012 18:17:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubPackage](
[SupplierId] [nvarchar](15) NOT NULL,
[SubPackageId] [nvarchar](30) NOT NULL,
[MainPackageSupplierId] [nvarchar](15) NOT NULL,
[MainPackageId] [nvarchar](30) NOT NULL,
[PartNumber] [nvarchar](30) NOT NULL,
[PackageQuantity] [int] NOT NULL,
[SubPackageBalanceTypeId] [int] NULL,
[FifoTime] [datetime] NOT NULL,
[MaterialHandlingUnit] [nvarchar](20) NOT NULL,
[AssemblyOrderId] [int] NOT NULL,
[SerialNumber] [nvarchar](30) NULL,
[ASNId] [int] NOT NULL,
[SupplierBatchNumber] [nvarchar](30) NULL,
[OriginalSupplierId] [nvarchar](15) NOT NULL,
[OriginalPackageId] [nvarchar](30) NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
[TransportDeliveryTime] [datetime] NULL,
[EmballageType] [nvarchar](6) NULL,
[AssemblyOrder] [nvarchar](10) NULL,
[CPOId] [nvarchar](10) NULL,
[LoosePartItemId] [int] NULL,
[IsLogical] [bit] NOT NULL,
CONSTRAINT [PK_SubPackage] PRIMARY KEY CLUSTERED
(
[SupplierId] ASC,
[SubPackageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SubPackage] WITH CHECK ADD CONSTRAINT [CustomerOrderSequence_SubPackage_FK1] FOREIGN KEY([CPOId])
REFERENCES [dbo].[CustomerOrderSequence] ([CPOId])
GO
ALTER TABLE [dbo].[SubPackage] CHECK CONSTRAINT [CustomerOrderSequence_SubPackage_FK1]
GO
ALTER TABLE [dbo].[SubPackage] WITH CHECK ADD CONSTRAINT [SubPackage_LoosePartItem_FK1] FOREIGN KEY([LoosePartItemId])
REFERENCES [dbo].[LoosePartItem] ([Id])
GO
ALTER TABLE [dbo].[SubPackage] CHECK CONSTRAINT [SubPackage_LoosePartItem_FK1]
GO
========================================
/****** Object: Table [dbo].[SubPackageStatusCode] Script Date: 05/18/2012 18:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubPackageStatusCode](
[SubPackageStatusCodeId] [int] NOT NULL,
[Export] [bit] NOT NULL,
[Description] [nvarchar](50) NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
CONSTRAINT [PK_PackageStatusCode] PRIMARY KEY CLUSTERED
(
[SubPackageStatusCodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Hi Vijay,
I tested your sample script but could not locate the bug. May be there are some problems with the data.
Given below are the sample scripts along with sample data, which were recovered successfully.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Drop TABLE [dbo].[SubPackageStatus]
GO
Drop TABLE [dbo].[SubPackage]
GO
CREATE TABLE [dbo].[SubPackage](
[SupplierId] [nvarchar](15) NOT NULL,
[SubPackageId] [nvarchar](30) NOT NULL,
[MainPackageSupplierId] [nvarchar](15) NOT NULL,
[MainPackageId] [nvarchar](30) NOT NULL,
[PartNumber] [nvarchar](30) NOT NULL,
[PackageQuantity] [int] NOT NULL,
[SubPackageBalanceTypeId] [int] NULL,
[FifoTime] [datetime] NOT NULL,
[MaterialHandlingUnit] [nvarchar](20) NOT NULL,
[AssemblyOrderId] [int] NOT NULL,
[SerialNumber] [nvarchar](30) NULL,
[ASNId] [int] NOT NULL,
[SupplierBatchNumber] [nvarchar](30) NULL,
[OriginalSupplierId] [nvarchar](15) NOT NULL,
[OriginalPackageId] [nvarchar](30) NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
[TransportDeliveryTime] [datetime] NULL,
[EmballageType] [nvarchar](6) NULL,
[AssemblyOrder] [nvarchar](10) NULL,
[CPOId] [nvarchar](10) NULL,
[LoosePartItemId] [int] NULL,
[IsLogical] [bit] NOT NULL,
CONSTRAINT [PK_SubPackage] PRIMARY KEY CLUSTERED
(
[SupplierId] ASC,
[SubPackageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Drop TABLE [dbo].[SubPackageStatusCode]
GO
CREATE TABLE [dbo].[SubPackageStatusCode](
[SubPackageStatusCodeId] [int] NOT NULL,
[Export] [bit] NOT NULL,
[Description] [nvarchar](50) NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
CONSTRAINT [PK_PackageStatusCode] PRIMARY KEY CLUSTERED
(
[SubPackageStatusCodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SubPackageStatus](
[PackageSupplierId] [nvarchar](15) NOT NULL,
[PackageId] [nvarchar](30) NOT NULL,
[SubPackageStatusCodeId] [int] NOT NULL,
[PartNumber] [nvarchar](30) NOT NULL,
[UserId] [nvarchar](12) NOT NULL,
[StatusTime] [datetime] NOT NULL,
[CreatedBy] [nvarchar](12) NOT NULL,
[CreatedTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](12) NULL,
[ModifiedTime] [datetime] NULL,
[ObjectVersion] [int] NOT NULL,
CONSTRAINT [PK_PackageStatus] PRIMARY KEY CLUSTERED
(
[PackageSupplierId] ASC,
[PackageId] ASC,
[SubPackageStatusCodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SubPackageStatus] WITH NOCHECK ADD CONSTRAINT [FK_SubPackage_SubPackageStatus] FOREIGN KEY([PackageSupplierId], [PackageId])
REFERENCES [dbo].[SubPackage] ([SupplierId], [SubPackageId])
GO
ALTER TABLE [dbo].[SubPackageStatus] CHECK CONSTRAINT [FK_SubPackage_SubPackageStatus]
GO
ALTER TABLE [dbo].[SubPackageStatus] WITH NOCHECK ADD CONSTRAINT [FK_SubPackageStatusCode_SubPackageStatus] FOREIGN KEY([SubPackageStatusCodeId])
REFERENCES [dbo].[SubPackageStatusCode] ([SubPackageStatusCodeId])
GO
ALTER TABLE [dbo].[SubPackageStatus] CHECK CONSTRAINT [FK_SubPackageStatusCode_SubPackageStatus]
GO
INSERT INTO [test].[dbo].[SubPackageStatusCode]
([SubPackageStatusCodeId]
,[Export]
,[Description]
,[CreatedBy]
,[CreatedTime]
,[ModifiedBy]
,[ModifiedTime]
,[ObjectVersion])
VALUES
(2 —
,1 —
,Replicate(‘A’,50)–
,Replicate(‘B’,12) —
,Getdate() —
,Replicate(‘A’,12) —
,Getdate() —
,1)– )
GO
INSERT INTO [test].[dbo].[SubPackage]
([SupplierId]
,[SubPackageId]
,[MainPackageSupplierId]
,[MainPackageId]
,[PartNumber]
,[PackageQuantity]
,[SubPackageBalanceTypeId]
,[FifoTime]
,[MaterialHandlingUnit]
,[AssemblyOrderId]
,[SerialNumber]
,[ASNId]
,[SupplierBatchNumber]
,[OriginalSupplierId]
,[OriginalPackageId]
,[CreatedBy]
,[CreatedTime]
,[ModifiedBy]
,[ModifiedTime]
,[ObjectVersion]
,[TransportDeliveryTime]
,[EmballageType]
,[AssemblyOrder]
,[CPOId]
,[LoosePartItemId]
,[IsLogical])
VALUES
(Replicate(‘A’,15)–
,Replicate(‘B’,30)–
,Replicate(‘C’,15)–
,Replicate(‘E’,30)–
,Replicate(‘F’,30) —
,4–
,5–
,Getdate() —
,Replicate(‘G’,20) —
,6–
,Replicate(‘H’,30) —
,7 —
,Replicate(‘I’,30) —
,Replicate(‘J’,15) —
,Replicate(‘K’,30) —
,Replicate(‘L’,12) —
,Getdate() —
,Replicate(‘M’,12) —
,Getdate() —
,8–
,Getdate() —
,Replicate(‘N’,6) —
,Replicate(‘O’,10) —
,Replicate(‘P’,10) —
,9–
,1 )–)
GO
INSERT INTO [test].[dbo].[SubPackageStatus]
([PackageSupplierId]
,[PackageId]
,[SubPackageStatusCodeId]
,[PartNumber]
,[UserId]
,[StatusTime]
,[CreatedBy]
,[CreatedTime]
,[ModifiedBy]
,[ModifiedTime]
,[ObjectVersion])
VALUES
(Replicate(‘A’,15)
,Replicate(‘B’,30)
,2
,Replicate(‘C’,30)
,Replicate(‘D’,12)
,Getdate()
,Replicate(‘E’,12)
,Getdate()+15
,Replicate(‘F’,12)
,Getdate() +30
,3)
GO
Select * from [SubPackage]
Select * from [SubPackageStatus]
Select * from [SubPackageStatusCode]
GO
Delete from [SubPackageStatus]
GO
EXEC Recover_Deleted_Data_Proc ‘dbo.SubPackageStatus’
Thanks
Imran
Dear Imran,
when i execute that procedure it wil give following output.
(8 row(s) affected)
(8 row(s) affected)
Msg 536, Level 16, State 5, Procedure Recover_Deleted_Data_Proc1, Line 162
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
because some table fields include blank space,no records in that.
what is solution for that.if we have blank field in table?.
Dear Imran,
when i execute above procedure then it gives following output.
(8 row(s) affected)
(8 row(s) affected)
Msg 536, Level 16, State 5, Procedure Recover_Deleted_Data_Proc1, Line 162
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
I tested on different tables and i got deleted records but in some tables i found that if any field include blank space or empty field then it will gives above output.plese tell me solution for that.
Hi Govind,
I tried to create few samples as per your comments on SQL Server 2005 and recovered it successfully.Given below are the scripts.
CREATE TABLE [dbo].[Student](
[Sno] [int] NULL,
[Student name] [varchar](50) NULL,
[Date of Birth] datetime null,
[Grade] [char] (4) null,
[Marks] [int] NULL)
GO
Insert into dbo.[Student] values (1,’Bob jerry’,’1974-12-31′,’VI’,89)
Insert into dbo.[Student] values (1,NULL,NULL,”,0)
Insert into dbo.[Student] values (NULL,NULL,NULL,”,NULL)
Insert into dbo.[Student] values (NULL,”,”,”,NULL)
Can you please post your test table scripts and insert into statements along with SQL Environment.
Thank you.
Imran
Hi Imran
please check following code that i tried
create table emp0
(
eno int,
ename varchar(20)
)
=======================
insert into emp0 values(101,”)
=========================
delete from emp0
=========================
exec Recover_Deleted_Data_Proc1 ’emp0′
Info about my SQL Environment:-
Microsoft SQL Server Management Studio Express Version 9.00.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.3053
Operating System 5.1.2600
Hi Govind,
Thank you for your valuable feedback.The issue has been resolved.Please check.
Imran
hi imran
1) I checked but dosen’t found above solution pls tell me again.
2)And also when there is same records more than one time then it will gives only one record from that.
eg.
create table emp10
(
eno int,
ename varchar(10)
)
insert into emp10 values(101,’govind’)
insert into emp10 values(101,’govind’)
insert into emp10 values(101,’govind’)
insert into emp10 values(101,’govind’)
========================
delete from emp10
========================
exec Recover_Deleted_Data_Proc ’emp10′
OUT PUT:-
101 govind
only one record, but i deleted four records.
Hi Govind,
Appreciate your feedback. Your both issues are fixed in the script and I am sure it will add value in the script.
Thanks
Imran
Thank you imran…..
hi Imran.
Is there any procedure for getting updated and deleted records with date and times.for tracking user activities.
Hi Muhammad,
You done a really brilliant job. You are deserved to appreciable.
By the way i have run your SP in my database and tested some Sample tables. For some table it is working fine. But some other not. I’m getting some error like below after Executing the SP.
(8 row(s) affected)
(22 row(s) affected)
Msg 8115, Level 16, State 6, Procedure Recover_Deleted_Data_Proc, Line 163
Arithmetic overflow error converting varbinary to data type numeric.
The statement has been terminated.
(0 row(s) affected)
Please verify it.
Thanks
Rajesh G
Hi Rajesh,
Thank you for your feedback. Can you please post the numeric values (for example 9876.336) that you are trying to recover ?
Imran
Hi Imran,
I tried the very first query but it didn’t give any result.
i’ve tested it sql 2008 it’s ok. But it not recoverd the record i deleted direct from sql managerment when i edit table.
Hello please help me!
I’ve run it show error:
“Msg 210, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 163
Conversion failed when converting datetime from binary/varbinary string.”
my table struction is:
CREATE TABLE [dbo].[tblCongViec](
[CV_ID] [int] IDENTITY(1,1) NOT NULL,
[CV_PID] [int] NULL,
[CV_VB_ID] [int] NULL,
[CV_VB_Ten] [nvarchar](250) NULL,
[CV_MaCongViec] [nvarchar](50) NULL,
[CV_MaCongViec_So] [nvarchar](50) NULL,
[CV_MaCongViec_KH] [nvarchar](50) NULL,
[CV_Loai] [bit] NULL,
[CV_Ten] [nvarchar](450) NULL,
[CV_Loai_ID] [int] NULL,
[CV_Loai_Ten] [nvarchar](150) NULL,
[CV_NgayCongVan] [datetime] NULL,
[CV_TrichYeu] [nvarchar](450) NULL,
[CV_NguoiNhapViec] [nvarchar](450) NULL,
[CV_NgayGiaoViec] [datetime] NULL,
[CV_NguoiGiaoViec] [nvarchar](50) NULL,
[CV_NguoiGiaoViec_Ten] [nvarchar](150) NULL,
[CV_LDVP_Username] [nvarchar](50) NULL,
[CV_LDVP_Ten] [nvarchar](450) NULL,
[CV_LDVP_NgayYKien] [datetime] NULL,
[CV_LDVP_YKien] [nvarchar](450) NULL,
[CV_LDXL_Username] [nvarchar](50) NULL,
[CV_LDXL_Ten] [nvarchar](250) NULL,
[CV_LDXL_Ngay] [datetime] NULL,
[CV_LDXL_YKien] [nvarchar](450) NULL,
[CV_HanHoanThanh] [datetime] NULL,
[CV_LanhDaoDuyet] [bit] NULL,
[CV_DaXem] [bit] NULL,
[CV_DungViec] [bit] NULL,
[CV_HoanThanh] [bit] NULL,
[CV_MucDoCongViec] [tinyint] NULL,
[CV_MucDoKhan] [tinyint] NULL,
[CV_NhacViecLoai] [tinyint] NULL,
[CV_NhacViecThoiGian] [int] NULL,
[CV_DaGiao] [bit] NULL,
[CV_NguoiXacThuc] [nvarchar](50) NULL,
[CV_XacThuc] [bit] NULL,
[CV_Draff] [bit] NULL,
[CV_RowId] [uniqueidentifier] NULL,
[CV_NguoiTao] [nvarchar](50) NULL,
[CV_NgayTao] [datetime] NULL,
[CV_NguoiCapNhat] [nvarchar](50) NULL,
[CV_NgayCapNhat] [datetime] NULL,
CONSTRAINT [PK_tblCongViec] PRIMARY KEY CLUSTERED
(
[CV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
my sql server version is 2008.
Hi Pham,
Can you please post the insert into statement of this table along with some sample data for testing purpose.
Thank you.
Imran
Hello Muhammad Imran
My insert statement is:
create proc [dbo].[sp_tblCongViec_Insert_InsertNormal_hungpm]
(
@CV_PID int,
@CV_Loai bit,
@CV_Ten nvarchar(450),
@CV_Loai_ID int,
@CV_Loai_Ten nvarchar(150),
@CV_NgayCongVan datetime,
@CV_TrichYeu nvarchar(450),
@CV_NguoiNhapViec nvarchar(450),
@CV_NgayGiaoViec datetime,
@CV_NguoiGiaoViec nvarchar(50),
@CV_LDVP_Username nvarchar(50),
@CV_LDVP_Ten nvarchar(450),
@CV_LDVP_YKien nvarchar(450),
@CV_LDXL_Username nvarchar(50),
@CV_LDXL_Ten nvarchar(250),
@CV_LDXL_Ngay datetime,
@CV_LDXL_YKien nvarchar(450),
@CV_HanHoanThanh datetime,
@CV_DungViec bit,
@CV_HoanThanh bit,
@CV_MucDoCongViec tinyint,
@CV_MucDoKhan tinyint,
@CV_NhacViecLoai tinyint,
@CV_NhacViecThoiGian int,
@CV_DaGiao bit,
@CV_XacThuc bit,
@CV_Draff bit,
@CV_RowId uniqueidentifier,
@CV_NguoiTao nvarchar(50),
@CV_NgayTao datetime,
@CV_NguoiCapNhat nvarchar(50),
@CV_NgayCapNhat datetime,
@CV_NguoiGiaoViec_Ten nvarchar(150),
@CV_LDVP_NgayYKien datetime,
@CV_NguoiXacThuc nvarchar(150),
@CV_LanhDaoDuyet bit,
@CV_MaCongViec nvarchar(50) = null,
@CV_MaCongViec_So nvarchar(50) = null,
@CV_MaCongViec_KH nvarchar(50) = null
)
as
insert into dbo.tblCongViec
( CV_PID, CV_Loai, CV_Ten, CV_Loai_ID, CV_Loai_Ten, CV_NgayCongVan, CV_TrichYeu, CV_NguoiNhapViec, CV_NgayGiaoViec, CV_NguoiGiaoViec, CV_LDVP_Username, CV_LDVP_Ten, CV_LDVP_YKien, CV_LDXL_Username, CV_LDXL_Ten, CV_LDXL_Ngay, CV_LDXL_YKien, CV_HanHoanThanh, CV_DungViec, CV_HoanThanh, CV_MucDoCongViec, CV_MucDoKhan, CV_NhacViecLoai, CV_NhacViecThoiGian, CV_DaGiao, CV_XacThuc, CV_Draff, CV_RowId, CV_NguoiTao, CV_NgayTao, CV_NguoiCapNhat, CV_NgayCapNhat, CV_NguoiGiaoViec_Ten, CV_LDVP_NgayYKien, CV_NguoiXacThuc, CV_LanhDaoDuyet, CV_MaCongViec, CV_MaCongViec_So, CV_MaCongViec_KH, CV_DaXem)
values
( @CV_PID, @CV_Loai, @CV_Ten, @CV_Loai_ID, @CV_Loai_Ten, @CV_NgayCongVan, @CV_TrichYeu, @CV_NguoiNhapViec, @CV_NgayGiaoViec, @CV_NguoiGiaoViec, @CV_LDVP_Username, @CV_LDVP_Ten, @CV_LDVP_YKien, @CV_LDXL_Username, @CV_LDXL_Ten, @CV_LDXL_Ngay, @CV_LDXL_YKien, @CV_HanHoanThanh, @CV_DungViec, @CV_HoanThanh, @CV_MucDoCongViec, @CV_MucDoKhan, @CV_NhacViecLoai, @CV_NhacViecThoiGian, @CV_DaGiao, @CV_XacThuc, @CV_Draff, @CV_RowId, @CV_NguoiTao, @CV_NgayTao, @CV_NguoiCapNhat, @CV_NgayCapNhat, @CV_NguoiGiaoViec_Ten, @CV_LDVP_NgayYKien, @CV_NguoiXacThuc, @CV_LanhDaoDuyet, @CV_MaCongViec, @CV_MaCongViec_So, @CV_MaCongViec_KH,0 )
go
my data in the excle file you can download from this file
http://www.mediafire.com/view/?afkainmohsx48k5
Thank you so much
Hi Pham,
I tested your sample table along with the data.It is working fine. Can you please post your collation also ?
Thank you.
Imran
Hi Imran,
If i have a backup of the database & i restored to some other server is it possible to get the deleted rows in that case
Please suggest
Regards
Asit
Hi Asit,
If you have a full back and you restored it into any server.You can recover the deleted data.
But, the only thing you need to make sure that you did not delete or truncate the log file.
Imran
Hi Imran,
This indeed a life saving script for the developers. I just have one query , if i have a database backup from where the data is deleted and restored it to another server. Do i be able to recover the data ???
Regards
Asit
Hi,
I’ve few queries.
1. Does it work irrespective of the recovery model of the database.
2. Is there any time frame for the deleted data which can be recovered like if I want to recover data which was deleted 3 weeks back… will it work.
3. And lets say my db was on Server A and I took the backup and restored it on server B and want to recover data on server B using your script but the data was deleted on server A.. will it work.
Thanks,
Rohit
Hi Rohit,
Thank you for your feedback. Given below are my feedback.
1. No, It will not work for simple recovery model.
2. As long As you have the log , you can recover it, there is no time limitation.
3. If it is a Full back up , yes you can recover the delete data from any server.
Imran.
Hi Muhammad,
I accidently delete the whole files.. want to recover it from online hosting database… i tried this
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)WHERE
AllocUnitName =’dbo.VASMessageLog’ AND Context IN
(‘VAS’, ‘pwd’) AND Operation in (‘LOP_DELETE_ROWS’)
But the error it gave is this..
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
What can be done to retrieve data.
Hi Udbodh,
It seems that you don’t have the permission to execute ‘fn_dblog’ function at the server
and if your database is hosted online, so they must have back up and you can retrieve your data from the backup.
Regards,
Imran
Hi,
I am having compatability as SQL SERVER 2008 (100).
still it gives error
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Hi Santhosh,
I checked this script again on SQL SERVER 2008.It is working fine.Can you please print @Compatibility_Level variable on line # 029 and check its value.
Thank you.
Imran
Salam Muhammad,
First Thanks for this Script.
I m trying to Run this script on SQL Server 2008 R2. But it is giving me same ERROR “Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)” Can u please Help me.
Thanks,
Hi Mazhar,
Thank you for your feedback. Please print @Compatibility_Level at line number 029 and check it should be greater than 80.
Imran
I ran the script on my database but I am getting this: (8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 178
There is no data in the log as per the search criteria.
My transaction log is about 800 megs so I’m not sure what it can’t find?
Hi Alan,
This script search the deleted data in the transaction log via fn_dblog().
If it doesn’t get any records then it give you this error.
For Example : if you execute, this ” EXEC Recover_Deleted_Data_Proc ‘test’,’dbo.Test_Table’ ,’2011/12/01′,’2012/01/30′ ”
So this procedure will search the deleted data in the given date and if it doesn’t find any record will give you this info message.
Regards,
Imran
i have Alan problem
my log is’nt empty but am getting this: (8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 178
why???
Hi Imran;
I have a table with follow structure and Arabic_CI_AS in sql 2008 and delete 2 record from Table and want to recover but this script last more than 8 hour and dont end pleas Help Me …
Thank
CREATE TABLE [dbo].[tbl_inter_let](
[interlet_no] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[in_date] [varchar](10) NULL,
[ind_date] [varchar](10) NULL,
[ind_noOld] [int] NULL,
[ind_no] [varchar](50) NULL,
[snd_type] [int] NOT NULL,
[eghdam] [int] NOT NULL,
[tabaghe] [int] NOT NULL,
[prs_writer] [int] NULL,
[paraf_chs] [int] NULL,
[paraf_prs] [int] NULL,
[from_chs] [int] NULL,
[from_prs] [int] NULL,
[paraf_date] [char](10) NULL,
[tayeed_paraf] [bit] NULL,
[to_chs] [int] NULL,
[to_prs] [int] NULL,
[subject] [varchar](200) NULL,
[let_title] [varchar](200) NULL,
[let_body] [image] NULL,
[Sign_prs] [int] NULL,
[sign_status] [int] NULL,
[sign_date] [varchar](10) NULL,
[let_status] [int] NULL,
[p_let_type] [int] NULL,
[p_let_no] [int] NULL,
[p_date] [varchar](10) NULL,
[p_inlet_no] [int] NULL,
[p_outlet_no] [int] NULL,
[p_interlet_no] [int] NULL,
[image_id] [int] NULL,
[letter_id] [int] NULL,
[image_id2] [int] NULL,
[let_bodyType] [varchar](10) NULL,
CONSTRAINT [PK_tbl_inter_let] PRIMARY KEY CLUSTERED
(
[interlet_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Hi Zahra,
Can you please post some sample data as well ?
Thank you.
Imran
Hi Zahra,
Now, this script is compatible with the “Arabic_CI_AS” collation also.
Thank you for your support.
Imran
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 181
There is no data in the log as per the search criteria
Hi Haseeb,
Please execute the given below query to check that the data is available in your log or not.
Kindly replace your table name with “tableName”.
Imran
Select * 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(” + ‘tableName’ + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
I have executed you query and got 20 rows in result.
I have executed the stored procedure again for record recover but still got this error
“Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 181
There is no data in the log as per the search criteria”
Hi Haseeb,
Please try the procedure without date range like :
EXEC Recover_Deleted_Data_Proc ‘DB Name’,’dbo.Table Name’
OR you can comment from line # 150 till line # 154 and execute it again, so it will pick up all the records without date range.
Hi Irman
First of all… thank you for sharing your code! its keeping our hopes up!
Wondering if you could help me out.
I run the stored proc and it hangs for hours…. I ran a query to get the info from the log,and it does return the rows in hex format.
My setting are set to:
Compatibility level: sql server 2005(90)
Collation: latin1_General_CP1_CI_AS
Recovery model: simple
Apreciate it and hope to hear from you soon!
Cheers,
Pancho
Hi Pancho,
Thank you for your feedback. Unfortunately, it will not work on simple recovery model.
Imran
It’s amaging !! It worked for me like a charm 🙂
Hi Muhammad,
Can you help me please, i get this error:
EXEC Recover_Deleted_Data_Proc ‘MyDB’, ‘dbo.MyTable’, ‘2012-07-28’, ‘2012-07-29’
Or
EXEC Recover_Deleted_Data_Proc ‘MyDB’, ‘dbo.MyTable’
Result error:
—
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 186
There is no data in the log as per the search criteria
—
Originaly Compatibility level: sql server 2000(80)
i set to sql server 2005(90)
Query:
Select * 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(‘MyTable’))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
There is no results.
Thank you, Santy
Hi Santy,
It means that your log is empty and this procedure recovers the data from the log.
Please check your recovery model. It should be Full.
Thank you.
Imran
Hi Muhammad
Have you tried this in MSSQL 2012
Garth
Hi Garth,
I tested this procedure on SQL Server 2012 also. Please let me know, if you are facing any problem.
Thank you
Imran
Hi Muhammad
This is the errors:
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 31
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 31
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Hi Garth,
Can you please print @Compatibility_Level on line # 28 and check it should be more than 80. Because I validated it on line # 29.
Imran
Hi Imran
The compatibility_level of the db that i want to check is 90 the rest is master = 110
Hi Garth,
If the compatibility_level is 90 then it should not alert error message because it is validating only if it is less than 80.
Imran
As a group host, keep up the good work
Hi Imran
this is the error that i get.
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 181
There is no data in the log as per the search criteria
thanks
garth
Hi Imran,
It is working now…
thanks for the posts.
Keep up the good work.
Garth
hi , i also use this procedure. But i have the following error
Msg 229, Level 14, State 5, Procedure Recover_Deleted_Data_Proc, Line 77
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 187
There is no data in the log as per the search criteria
I use ms sql 2008
Could you please help what i will do next?
Thanks in Advance
Guna
Hi Guna,
You don’t have the permission to access “fn_dblog” in SQL server 2008. Kindly grant that access to the user (Security >> Logins >> Users >> properties) and then execute this procedure again.
Thank you.
Imran
Hi Muhammad,
I tried , got record. Thanks much.
Thanks,
GUna
Hello, I need to use your sproc but i am facing an issue when i try to create it.. i am having a message about cant convert varchar to datetime, my sqlserver is in spanish language, the message is:
Msg 242, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 0
The conversion from data type varchar to datetime produced a value out of range.
Msg 8114, Level 16, State 5, Procedure Recover_Deleted_Data_Proc, Line 0
Error converting data type varchar to datetime.
And the sproc is created but i have the same message when i tried to execute it
Hi Jorge,
Can you please post your sql server & collation details. Also the parameter of this procedure.
Imran.
Thanks u sooo much dude…. Working perfectly… 🙂
Hi Imran,
Thanks for this post. I am trying to run a recovery in a SQL Server 2008 database. This is the command I executed;
EXEC Recover_Modified_Data_Proc ‘salonF11′,’dbo.annotation’,’2012/08/29 00:00′,’2012/08/29 11:00′
and this is my output:
Msg 2714, Level 16, State 3, Procedure Recover_Modified_Data_Proc, Line 643
There is already an object named ‘Recover_Modified_Data_Proc’ in the database.
(0 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2137 row(s) affected)
(2137 row(s) affected)
(7 row(s) affected)
(7 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(14 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 433
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
SELECT [annotation_id],[document_id],[user_id],[comment_text],[comment_area],[comment_positivity],[comment_time],[reply_to],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([annotation_id],[document_id],[user_id],[comment_text],[comment_area],[comment_positivity],[comment_time],[reply_to],[Update Statement])) 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])
(0 row(s) affected)
The types of the columns are as follows: comment_text is TEXT, comment_area is VARCHAR(255), and comment_time is DATETIME, and everything else is an int.
Can you help me figure out what’s wrong/how to fix it?
Thanks in advance,
Dev
Hi Dev,
Text data type is not yet handled in this procedure. Due to this it is enable to recover the data.
Thank you.
Imran
Thank you for your response. Can you give me some guidance as to how to implement support for the text data type in this procedure? Is it just modifying step 6? Any help you can provide would be appreciated.
Hi Dev,
I did the recovery from text data type in this article.(https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/)
I think, if we remove step six it will not workout. Because SQL Server keeps log of text data type in data pages not in the log. I need to do some research also to implement text data type recovery in this procedure.
Thank you.
Imran
Hi Imran,
Thanks for this post. I am trying to run a recovery in a SQL Server 2008 database. This is the command I executed;
EXEC Recover_Modified_Data_Proc ’salonF11’,’dbo.annotation’,’2012/08/29 00:00’,’2012/08/29 11:00’
and this is my output:
Msg 2714, Level 16, State 3, Procedure Recover_Modified_Data_Proc, Line 643
There is already an object named ’Recover_Modified_Data_Proc’ in the database.
(0 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2097 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2142 row(s) affected)
(2137 row(s) affected)
(2137 row(s) affected)
(7 row(s) affected)
(7 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(14 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 433
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
SELECT [annotation_id],[document_id],[user_id],[comment_text],[comment_area],[comment_positivity],[comment_time],[reply_to],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([annotation_id],[document_id],[user_id],[comment_text],[comment_area],[comment_positivity],[comment_time],[reply_to],[Update Statement])) 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])
(0 row(s) affected)
The types of the columns are as follows: comment_text is TEXT, comment_area is VARCHAR(255), and comment_time is DATETIME, and everything else is an int.
Can you help me figure out what’s wrong/how to fix it?
Thanks in advance,
Dev
Hi Imran, This is great i tried the sample and it working ok the only challenge is when i try it in a large database its taking long to execute is this expected?
Kim
Hi Kim,
Thank you for your feedback. Yes , you are right.It depends upon the number of records. That’s why I recommend to use it with the date range.
Thank you.
Imran
Your posts to raresql.com are incredibly helpful and the only example that I have seen to actually reconstruct items faithfully from the log. More specifically, what I found especially useful were the following posts:
How to recover deleted data from SQL Server 2000
How to recover deleted data from SQL Server
How to recover modified records from SQL Server without Backup
How to recover modified records from SQL Server without Backup Explanation
How to recover truncated data from SQL Server without Backup
Thank you very much for your helpful insights.
How does one handle inserts? Or, more specifically, how are the fn_dblog fields:
“RowLog Contents 0” ,
“RowLog Contents 1” ,
“RowLog Contents 2” ,
“RowLog Contents 3” ,
“RowLog Contents 4” ,
“Log Record”
utilized for an insert?
Hi Dann,
Thank you for your feedback. I will develop script for insert as well and let you know.
Imran
Hi Muhammad,
I have to integrate Gmail integration in my asp.net project. Could u plz tel how to do that.(Like way2sms gmail integration).
Thanks,
Guna
Hi Imran,
First of all a great article I found,very nicely done.I’m getting some error.Could you recheck my query?
DROP PROCEDURE Recover_Deleted_Data_Proc
GO
Create PROCEDURE Recover_Deleted_Data_Proc
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(Max),
@Date_From DATETIME,
@Date_To DATETIME
AS
DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(Max)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @Compatibility_Level INT
SELECT @Compatibility_Level=dtb.compatibility_level
FROM
master.sys.databases AS dtb WHERE dtb.name=@Database_Name
IF ISNULL(@Compatibility_Level,0)<=80
BEGIN
RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
RETURN
END
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+'.'+[TABLE_NAME]=@SchemaName_n_TableName)=0
BEGIN
RAISERROR('Could not found the table in the defined database',16,1)
RETURN
END
DECLARE @bitTable TABLE
(
[ID] INT,
[Bitvalue] INT
)
–Create table to set the bit position of one byte.
INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128
–Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
[Row ID] INT IDENTITY(1,1),
[RowLogContents] VARBINARY(8000),
[AllocUnitID] BIGINT,
[Transaction ID] NVARCHAR(Max),
[FixedLengthData] SMALLINT,
[TotalNoOfCols] SMALLINT,
[NullBitMapLength] SMALLINT,
[NullBytes] VARBINARY(8000),
[TotalNoofVarCols] SMALLINT,
[ColumnOffsetArray] VARBINARY(8000),
[VarColumnStart] SMALLINT,
[Slot ID] INT,
[NullBitMap] VARCHAR(MAX)
)
–Create a common table expression to get all the row data plus how many bytes we have for each row.
;WITH RowData AS (
SELECT
[RowLog Contents 0] AS [RowLogContents]
,[AllocUnitID] AS [AllocUnitID]
,[Transaction ID] AS [Transaction ID]
–[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] –@FixedLengthData
— [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as [TotalNoOfCols]
–[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]
–[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]
–[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) 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 (0x10,0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) 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 (0x10,0x30,0x70)
THEN (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))
+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) * 2))
ELSE null End AS [VarColumnStart]
,[Slot ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DELETE'
And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To
)),
–Use this technique to repeate the row till the no of bytes of the row.
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
INSERT INTO @DeletedRecords
SELECT RowLogContents
,[AllocUnitID]
,[Transaction ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
,[Slot ID]
—Get the Null value against each column (1 means null zero means not null)
,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, 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
IF (SELECT COUNT(*) FROM @DeletedRecords)=0
BEGIN
RAISERROR('There is no data in the log as per the search criteria',16,1)
RETURN
END
DECLARE @ColumnNameAndData TABLE
(
[Row ID] int,
[Rowlogcontents] varbinary(Max),
[NAME] sysname,
[nullbit] smallint,
[leaf_offset] smallint,
[length] smallint,
[system_type_id] tinyint,
[bitpos] tinyint,
[xprec] tinyint,
[xscale] tinyint,
[is_null] int,
[Column value Size]int,
[Column Length] int,
[hex_Value] varbinary(max),
[Slot ID] int,
[Update] int
)
–Create common table expression and join it with the rowdata table
— to get each column details
/*This part is for variable data columns*/
–@RowLogContents,
–(col.columnOffValue – col.columnLength) + 1,
–col.columnLength
–)
INSERT INTO @ColumnNameAndData
SELECT
[Row ID],
Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(CASE WHEN leaf_offset30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) – POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
END)
END) AS [Column value Size],
(CASE WHEN leaf_offset30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])>30000
THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) –24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])<30000
THEN (CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart]))
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) 30000
THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
END)
END) AS [Column Length]
,(CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,
(
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) >30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) – POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
END)
–
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])>30000
THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) –24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])<30000
THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) 30000
THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
END)
) + 1,
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])>30000
THEN (Case When [System_type_id] In (35,34,99) Then 16 else 24 end) –24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])<30000
THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart]))
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) 30000
THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2))))
– ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
END)
)
END) AS hex_Value
,[Slot ID]
,0
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset1 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_bit1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit0
Order By nullbit
Declare @BitColumnByte as int
Select @BitColumnByte=CONVERT(INT, ceiling( Count(*)/8.0)) from @ColumnNameAndData Where [System_Type_id]=104
;With N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
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),
CTE As(
Select RowLogContents,[nullbit]
,[BitMap]=Convert(varbinary(1),Convert(int,Substring((REPLACE(STUFF((SELECT ‘,’ +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(hex_Value, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(hex_Value, n, 1) / [Bitvalue]) % 2)) END) –as [nullBitMap]
from N4 AS Nums
Join @ColumnNameAndData AS C ON n<=@BitColumnByte And [System_Type_id]=104 And bitpos=0
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',','')),bitpos+1,1)))
FROM @ColumnNameAndData D Where [System_Type_id]=104)
Update A Set [hex_Value]=[BitMap]
from @ColumnNameAndData A
Inner Join CTE B On A.[RowLogContents]=B.[RowLogContents]
And A.[nullbit]=B.[nullbit]
/**************Check for BLOB DATA TYPES******************************/
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @CurrentLSN INT
DECLARE @LinkID INT
DECLARE @Context VARCHAR(50)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @LCX_TEXT_MIX VARBINARY(MAX)
declare @temppagedata table
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
declare @pagedata table
(
[Page ID] sysname,
[File IDS] int,
[Page IDS] int,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
DECLARE @ModifiedRawData TABLE
(
[ID] INT IDENTITY(1,1),
[PAGE ID] VARCHAR(MAX),
[FILE IDS] INT,
[PAGE IDS] INT,
[Slot ID] INT,
[AllocUnitId] BIGINT,
[RowLog Contents 0_var] VARCHAR(Max),
[RowLog Length] VARCHAR(50),
[RowLog Len] INT,
[RowLog Contents 0] VARBINARY(Max),
[Link ID] INT default (0),
[Update] INT
)
DECLARE Page_Data_Cursor CURSOR FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID*/
SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
,[Slot ID],[AllocUnitId],NULL AS [RowLog Contents 0],NULL AS [RowLog Contents 0],Context
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')
AND Description Like '%Deallocated%'
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
AND [Transaction Name]='DELETE'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To
)
GROUP BY [Description],[Slot ID],[AllocUnitId],Context
UNION
SELECT [PAGE ID],[Slot ID],[AllocUnitId]
,Substring([RowLog Contents 0],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0]
,CONVERT(INT,Substring([RowLog Contents 0],7,2)),Context –,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Context IN ('LCX_TEXT_MIX') AND Operation in ('LOP_DELETE_ROWS')
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DELETE'
And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To
)
/****************************************/
OPEN Page_Data_Cursor
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @hex_pageid AS VARCHAR(Max)
/*Page ID contains File Number and page number It looks like 0001:00000130.
In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/
SET @Fileid=SUBSTRING(@ConsolidatedPageID,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)
IF @Context='LCX_PFS'
BEGIN
DELETE @temppagedata
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@fileid,@pageid,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
END
ELSE IF @Context='LCX_TEXT_MIX'
BEGIN
INSERT INTO @ModifiedRawData SELECT @ConsolidatedPageID,@fileid,@pageid,@Slotid,@AllocUnitID,NULL,0,CONVERT(INT,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,2)))),@LCX_TEXT_MIX,@LinkID,0
END
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
–The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
–This hex value is in string format
INSERT INTO @ModifiedRawData ([PAGE ID],[FILE IDS],[PAGE IDS],[Slot ID],[AllocUnitId]
,[RowLog Contents 0_var]
, [RowLog Length])
SELECT [Page ID],[FILE IDS],[PAGE IDS],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4))-2 ) as [Slot ID]
,[AllocUnitId]
,Substring((
SELECT
REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[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%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
FOR XML PATH('') ),1,1,'') ,' ','')
),1,20000) AS [Value]
,
Substring((
SELECT '0x' +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%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
FOR XML PATH('') ),1,1,'') ,' ','')
),7,4) AS [Length]
From @pagedata B
Where [Object] Like '%Memory Dump%'
Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]–,[Current LSN]
Order By [Slot ID]
UPDATE @ModifiedRawData SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 'varbinary(Max)')))
FROM @ModifiedRawData Where [LINK ID]=0
UPDATE @ModifiedRawData SET [RowLog Contents 0] =cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 'varbinary(Max)')
FROM @ModifiedRawData Where [LINK ID]=0
Update B Set B.[RowLog Contents 0] =
(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]
END)
,B.[Update]=ISNULL(B.[Update],0)+1
from @ModifiedRawData B
LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))
And A.[Link ID]=B.[Link ID]
LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
And C.[Link ID]=B.[Link ID]
Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)
Update B Set B.[RowLog Contents 0] =
(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]
END)
–,B.[Update]=ISNULL(B.[Update],0)+1
from @ModifiedRawData B
LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))
And A.[Link ID]B.[Link ID] And B.[Update]=0
LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
And C.[Link ID]B.[Link ID] And B.[Update]=0
Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)
UPDATE @ModifiedRawData SET [RowLog Contents 0] =
(Case When [RowLog Len]>=8000 Then
Substring([RowLog Contents 0] ,15,[RowLog Len])
When [RowLog Len]<8000 Then
SUBSTRING([RowLog Contents 0],15+6,Convert(int,Convert(varbinary(max),REVERSE(Substring([RowLog Contents 0],15,6)))))
End)
FROM @ModifiedRawData Where [LINK ID]=0
UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
–,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=[PAGE IDS]
AND Convert(int,Substring([hex_value],9,2)) =B.[Link ID]
Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And [Link ID] 0
UPDATE @ColumnNameAndData SET [hex_Value]=
(CASE WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN B.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN B.[RowLog Contents 0]
END)
–,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],5,4))))=B.[PAGE IDS] And B.[Link ID] =0
LEFT JOIN @ModifiedRawData C ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=C.[PAGE IDS] And C.[Link ID] =0
Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And (B.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)
UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
–,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
And Convert(int,Substring([hex_value],3,2))=[Link ID]
Where [System_Type_Id] In (35,34,99) And [Link ID] 0
UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
–,A.[Update]=A.[Update]+10
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
Where [System_Type_Id] In (35,34,99) And [Link ID] =0
UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
–,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],15,4))))=[PAGE IDS]
Where [System_Type_Id] In (35,34,99) And [Link ID] =0
Update @ColumnNameAndData set [hex_value]= 0xFFFE + Substring([hex_value],9,LEN([hex_value]))
–,[Update]=[Update]+1
Where [system_type_id]=241
CREATE TABLE [#temp_Data]
(
[FieldName] VARCHAR(MAX),
[FieldValue] NVARCHAR(MAX),
[Rowlogcontents] VARBINARY(8000),
[Row ID] int
)
INSERT INTO #temp_Data
SELECT NAME,
CASE
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) –NVARCHAR ,NCHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) –VARCHAR,CHAR
WHEN system_type_id IN (35) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) –Text
WHEN system_type_id IN (99) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) –nText
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) –TINY INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) –SMALL INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) — INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))– BIG INTEGER
WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) –DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) –SMALL DATETIME
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) — NUMERIC
WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) — DECIMAL
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) –MONEY,SMALLMONEY
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) — BIT
WHEN system_type_id =62 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) — FLOAT
When system_type_id =59 THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) –Real
WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘VARBINARY(8000)’)) = 0 THEN ‘0x’ ELSE ” END) +cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘varchar(max)’) — BINARY,VARBINARY
WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,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)’) –IMAGE
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) –UNIQUEIDENTIFIER
WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) –SYSNAME
WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) –XML
WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘VARBINARY(8000)’)) = 0 THEN ‘0x’ ELSE ” END) +cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘varchar(max)’) –TIMESTAMP
WHEN system_type_id=98 THEN (CASE
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value)))))) — INTEGER
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) — NUMERIC
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) –VARCHAR,CHAR
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) –UNIQUEIDENTIFIER
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) –DATETIME
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN ‘0x’+ SUBSTRING((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)’),11,LEN(hex_Value)) — BINARY,VARBINARY
END)
END AS FieldValue
,[Rowlogcontents]
,[Row ID]
FROM @ColumnNameAndData ORDER BY nullbit
–Create the column name in the same order to do pivot table.
DECLARE @FieldName VARCHAR(max)
SET @FieldName = STUFF(
(
SELECT ‘,’ + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id(” + @SchemaName_n_TableName + ”)
FOR XML PATH(”)), 1, 1, ”)
–Finally did pivot table and get the data back in the same format.
SET @sql = ‘SELECT ‘ + @FieldName + ‘ FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘)) AS pvt’
EXEC sp_executesql @sql
GO
–Execute the procedure like
–Recover_Deleted_Data_Proc ‘Database Name’,’Schema.table name’
–EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc ‘TestDatabase’,’dbo.Stud’ –,’2012-09-12′,’2012-09-12′
GO
–EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
–EXEC Recover_Deleted_Data_Proc ‘test’,’dbo.Test_Table’ ,’2011/12/01′,’2012/01/30′
–It will give you the result of all deleted records.
Hi Sandeep,
Can you please post the error message with SQK environment details.
Thank you.
Imran
The query is not working in following cases.
1) bulk DELETE(delete from )i.e without where clause
2) bulk UPDATE(update set =”)i.e without where clause
Please suggest what to do…
Hi Sandeep,
I tried this one and could not regenerate this bug. Can you please make few samples ?
Imran
3) First time it retrieves data but after a few seconds no data returned-why?
Hi Sandeep,
If your log size is small, or the recovery model is simple. So the log will be truncated. It all depends upon the availability of data in the log.
Imran
What recovery model suits for these kind of operation?
It should be FULL.
You may also examine older versions of the transaction log via function fn_dump_dblog():
http://sqlskills.com/BLOGS/PAUL/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx
I changed the both but still this problem persist and again bulk delete can not be recovered using this same script.
Hi Sandeep,
How many records did u try to recover? Can you please post the test table and records as well. So that I can test it.
Appreciate you support.
Imran
How to stop Log file to be truncated?
Hi Sandeep,
Either, it should be defined as a sufficient size (Restricted) or it should be set to auto growth (Unrestricted).
Imran
This is also important:
http://www.sqlservergeeks.com/articles/sql-server-bi/6/restrict-the-use-of-no_log-and-truncate_only
Thank you Dann for your valuable addition.
Imran
Why its not working for Update?
Hi Sandeep,
This script only recover the deleted data. If you need that how to recover from updated/modified records. This is the script. (https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/)
Imran
First of all I want to thank you for your great support.Secondly,For recovery of modified record script is not fully displayed in the above link.Its cut short from right side.Could you send me in my mail or something else?
Hi Sandeep,
Here is the link :
https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
Imran
Same problem persist-please help.Can’t get the total script.Its getting cut short from right side.
Sent it to your email.
Hello Muhammad!
Our SQL server is set to italian language. Maybe therefore executing the procedure results in the following error message:
// —— begin result ——
[8 row(s) affected]
Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 68
[Microsoft][SQL Server Native Client 10.0][SQL Server]La conversione di un tipo di dati nvarchar in datetime ha generato un valore non compreso
nell’intervallo dei valori consentiti.
L’istruzione è stata interrotta.
[Execution completed with error(s)]
// —— end result ——
Or is there another error? I restored today, 17 sept a backup from yesterday evening. Then I deleted records in some tables then I tried to run your procedure:
–EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc ‘sinfonia’,’dbo.focolarini’ ,’20120917′,’20120917′
Do you have any ideas why the procedure fails and how I can make it work?
Thanks for your great work and regards,
Klaus
Hi Klaus,
You should not pass the date parameter like this :
EXEC Recover_Deleted_Data_Proc ‘sinfonia’,’dbo.focolarini’ ,’20120917′,’20120917′
Kindly make it like this :
EXEC Recover_Deleted_Data_Proc ‘sinfonia’,’dbo.focolarini’ ,’2012-09-17′,’2012-09-17′.
Imran
Hello Imran!
When I do like you suggest:
EXEC Recover_Deleted_Data_Proc ‘sinfonia’,’dbo.focolarini’ ,’2012-09-17′,’2012-09-17′
Then I got the error message:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Errore durante la conversione del tipo di dati da varchar a datetime.
[Execution aborted]
So it seems that execution of the stored procedure doesn’t even start since this type of format for data is not allowed in italien systems.
Isn’t yyymmdd a more general way of writing data? indeed, when passing SQL static SQL statements from my app to the server I alwas write the dates in this way to not run into problems with interatinal settings.
I guess when you convert hex values to date or datetime values within your procedure our server doesn’t recognise the (date)strings created.
Strange seems to me that giving the dates in format ‘yyyy/mm/dd’ results in the same error but your expression CONVERT(NVARCHAR(11), [Begin Time]) returns the date in this format…
😐 Klaus
Hi Klaus,
I will check and get back to you. Thank you
Imran
Hi, Muhammad Imran, It is working fine. If I delete the record manually by issuing delete command (delete from tableName) I am able to recover the lost data. I data is deleted from edit editor=>Choose the record(s)=>Click on Delete. Not able to recover the lost data. Please could you help me.
Hi Abdul,
The reason behind this is if you delete table from query , Log mark it as “Delete” but when you delete it from editor it mark it as “user_transaction”.
Kindly replace line # 154 with given code and check.
And [Transaction Name] In (‘DELETE’,’user_transaction’)
I will test it in multiple environment and then update it in the post.
Thank you
Imran
Thank You very much. Replacing [Transaction Name]=‘DELETE’ with [Transaction Name] In (‘DELETE’,’user_transaction’) works for me. Please by any chance could you also help me, If you have a script to recover any other database object droped, which can be recover, if it is not included in the current backup.
Hi Abdul,
Thank you for your feedback. Unfortunately, I could not develop the script to recover drop objects till now.
Imran
Thank you very much for you help. I will get back to you I if have any problem that you could solve. You have a good one.
very very nice article..keep writing such articles
Imran my data is not geting recovered through this procedure. I have manually deleted the my table.
Hi Ashish,
Kindly replace line # 154 with given below code and check.
And [Transaction Name] In (‘DELETE’,’user_transaction’)
Thank you
Imran
Thank you.
Misu
Hi Muhammad
Thanks for your helpful post
I have a database in server A and i have a friday job that takes a full and a daily job that occurs every 5 minutes and takes log. my boss wants me to restore database on server B and keep the databae on server B ,sync with server A (for just reporting),the problem is that both of the sql servers are in windows authentication mode and they cant see each other so i cant have log shipping.
what i did is :
i use map network to see the drive on server A to have the backup files and restore full backup with standby (because we just want to select from it).
how can i restore all log files and keep it sync with server A?
i create a job and with use of EXEC xp_cmdshell ‘dir d:\’ ,insert the log backups in a table
then with a cursor i restore them one by one (i select only logs that were taken today)
and this job occures onec at night, but he wants a better way,he said find a way like registering backups(full and log) in server B.is it possible?or do you have better idea?
Hi Mahdis,
It can be done via Link Server / replication as well.
Thank you.
Imran
I am using sql 2005 and is getting the error
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
please help, my compatibility level is 90
Hi Garfield,
Please comment line # 30 till 34. Also, you can print @Compatibility_Level at line # 29.
Imran
Hi Imran, i dont know the code to print the compatibility level,
i tried
PRINT @Compatibility_Level
Hi Garfield,
This syntax is correct.
Imran
90
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
This is the current result.
Hi Garfield,
Unfortunately, It means that the data is not available in your log. If you have any backup that is not much older, so you can also try this method.
https://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
Imran
ok. i dont have a backup. have to do something else ???
Hi Imran
Is there any way to change the location of .tuf file in log shipping?
I am using sql server 2005 and by default it creates it in the .trn folder,but it just has read permission to this folder,so it cant create utf file and log shipping failed.
I dont want to copy trn files to secondary server.
I’m sorry for leaving log shipping question in this post.
Hi Mahdis,
It is fine you can post it here as well. But I need to create a scenario like this and check then I can revert it to you.
Cheers
Imran
Thanks a lot.Any help would be appreciated.
Hi Imran,
I’m getting this:
(5 row(s) affected)
Msg 515, Level 16, State 2, Procedure Recover_Deleted_Data_Proc, Line 207
Cannot insert the value NULL into column ‘NAME’, table ‘@ColumnNameAndData’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Thanks,
George
Hi George,
Which one sql version are you using ? also please post the data types you have in your table.
Imran
Hi Imran,
Same problem.
Msg 515, Level 16, State 2, Procedure Recover_Deleted_Data_Proc, Line 211
Cannot insert the value NULL into column ‘NAME’, table ‘@ColumnNameAndData’; column does not allow nulls. INSERT fails.
is there any solution
please note that I have sql server 2008 R2.
Hi Dharmesh,
Please check your table data types. May be one of the data types is not covered in this procedure.
Thank you
Imran
[…] my first article I wrote how to recover deleted data from SQL Server. I received queries inquiring, if it is possible to recover the deleted data, is it also possible to […]
thanx . i got the result.
I have 46k records deleted by mistake.. And i Have tried your procedure, after 2 and half hours i cancelled the statement as it was taking too long and impacting the database performance.
version INFORAMTION:
Microsoft SQL Server Management Studio 9.00.5000.00
Microsoft Analysis Services Client Tools 2005.090.5000.00
TABLE STRUCTURE:
CREATE TABLE [dbo].[Apmax_CallerId_Process](
[Req_No] [int] IDENTITY(1,1) NOT NULL,
[Telephone_No] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OldTelephone_No] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Id] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Activity_Status] [int] NULL,
[CallerID_Type] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Updt_Timestamp] [datetime] NULL,
[StartedTime] [datetime] NULL,
[Status] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Apmax_CallerId_Process] PRIMARY KEY CLUSTERED
(
[Req_No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Exec Recover_Deleted_Data_Proc ‘PAT_TEST’, ‘dbo.Apmax_CallerId_Process’
One change i have done in your proc is:
@Date_From DATETIME=’2012/10/29′,
@Date_To DATETIME =’2012/10/30′
Because, the records deleted only yesterday 2012 Oct 29th 10 pm.
Please let me know.
Thanks ofr your help.
Raj
Hi Raj,
It takes time to recover the deleted data, because it decode the hexadecimal data from database log to normal and then give it back to you. It is also taking time due to 46K records.
you can also use given below method to recover your data quickly.
https://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
Thank you.
Imran
Hi,
Using backup approach: I have two months old backup. I wonder still can restore or not ?
Thanks
Raj
Hi Raj,
I think two month old backup will not work out. You can use the same procedure but pick up 1000 records at a time.
Regards,
Imran
Please find the sample data here:
43996 3523477497 CCTHDT1 16 Add 2012-10-16 13:16:01.467 2012-10-16 11:16:02.237 SUCCESS
43997 3523601192 CCTHDT1 16 Add 2012-10-16 13:16:10.327 2012-10-16 11:16:11.070 SUCCESS
43998 3523833337 CCTHDT1 16 Add 2012-10-16 13:16:19.153 2012-10-16 11:16:19.933 SUCCESS
43999 3523908680 CCTHDT1 16 Add 2012-10-16 13:16:28.027 2012-10-16 11:16:28.770 SUCCESS
44000 3524332712 CCTHDT1 16 Add 2012-10-16 13:16:36.903 2012-10-16 11:16:37.633 SUCCESS
44001 3526202665 CCTHDT1 16 Add 2012-10-16 13:16:45.747 2012-10-16 11:16:46.510 SUCCESS
44002 3526229668 CCTHDT1 16 Add 2012-10-16 13:16:54.590 2012-10-16 11:16:55.363 SUCCESS
44003 3526294606 CCTHDT1 16 Add 2012-10-16 13:17:03.463 2012-10-16 11:17:04.200 SUCCESS
sorry i try to use you stored pocedure for a deleted record (like 35.000 record !) in one table … but is runnig from 12 hour …is its possible ….
Hi Filippo,
Thank you for your feedback. Due to 35K records this procedure will take some time to recover.
you can also use given below method to get the data quickly.
https://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
Imran
this is the table structure …..
CREATE TABLE [dbo].[ModelsStatements](
[staGUID] [uniqueidentifier] NOT NULL,
[bookID] [uniqueidentifier] NOT NULL,
[modGUID] [uniqueidentifier] NOT NULL,
[ageGUID] [uniqueidentifier] NOT NULL,
[staParentGUID] [uniqueidentifier] NOT NULL,
[staType] [nvarchar](50) NULL,
[staDescriptions] [nvarchar](250) NULL,
[staCreationDate] [datetime] NULL,
[staCausale] [nvarchar](50) NULL,
[staTotale] [float] NULL,
[staPercAgenzia] [float] NULL,
[staNettoAgenzia] [float] NULL,
[staPercModello] [float] NULL,
[staNettoModello] [float] NULL,
[staTotaleSpeseDaRimborsare] [float] NULL,
[staSaldoLavoro] [float] NULL,
[staPubblico] [bit] NOT NULL,
[staPagato] [bit] NOT NULL,
[staPagatoData] [datetime] NULL,
[staScadenzaData] [datetime] NULL,
[staPercAgMadre] [float] NULL,
[staImportoAgMadre] [float] NULL,
[staQuantita] [float] NULL,
[staImportoUnitario] [float] NULL,
[staInizioPeriodo] [datetime] NULL,
[staFinePeriodo] [datetime] NULL,
[staTranceParentGUID] [uniqueidentifier] NOT NULL,
[staUsage] [bit] NOT NULL,
[staTipoDataScadenza] [nvarchar](10) NULL,
[staCommisioneAgenzia] [float] NULL,
[staCommisioneAgenziaUnitaria] [float] NULL,
[staTranceNumero] [int] NOT NULL,
[staDataLavoro] [datetime] NULL,
[ownerGUID] [uniqueidentifier] NOT NULL,
[syncCheckKey] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Statements] PRIMARY KEY CLUSTERED
(
[staGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ModelsStatements] ADD CONSTRAINT [DF_ModelsStatements_staPubblico] DEFAULT ((0)) FOR [staPubblico]
GO
ALTER TABLE [dbo].[ModelsStatements] ADD CONSTRAINT [DF_ModelsStatements_staPagato] DEFAULT ((0)) FOR [staPagato]
GO
ALTER TABLE [dbo].[ModelsStatements] ADD CONSTRAINT [DF_ModelsStatements_staUsage] DEFAULT ((0)) FOR [staUsage]
GO
ALTER TABLE [dbo].[ModelsStatements] ADD CONSTRAINT [DF_ModelsStatements_staTranecNumero] DEFAULT ((0)) FOR [staTranceNumero]
GO
Wonderful article
Thanks
Imran,
A table having year data around 10k rows. After deleting the data be mistake, i could recover the data from old backup around 10 months. Now i need to recover only the last 2 months data. Is that possible form your procedure.? Is so, can you please let me know .. thanks
Nice work and thanks for sharing Muhammad. I can think of several occasions, over the past year
Unfortunately, I’m getting the following errors with your Test_Table example (SQL 2012 Developer Edition SP1):
Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 521
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 647
XML parsing: line 1, character 2, illegal xml character
In the interest of making this code easier for others to use, a few suggestions:
The code is not organized in a way that it is ready to execute. Execute examples are hidden at the bottom of both the procedure definition and the table definition. It would be easier, especially for new members of the community, to understand if they were split up into separate code blocks.
It would also help if there was a quick check if the procedure existed before attempting to drop and create it. You’ll get an error if the procedure doesn’t exist. Something like:
IF EXISTS (
SELECT NULL FROM sys.objects WHERE name = ‘Recover_Deleted_Data_Proc’
)
DROP PROCEDURE [dbo].[Recover_Deleted_Data_Proc];
Please don’t take offense to the suggestions. I know you’ve spent a significant amount of time getting this code fine tuned and hope you continue to support it.
Thanks again for sharing!
Hi Buddy,
I really appreciate your valuable feedback and will implement it as well. As far as your error concern, it seems that your error is due to some xml data type.
Thank you.
Imran
It would seem, but I’m using the Test_Table definition. I stepped through the code and found that the hex string that it is attempting to parse has no “†” in it. I pulled the hex value out into the variable @Value and tried a stripped down version of the Substring:
DECLARE @Value sysname = ‘0000000000000000: 08004e1f 0000f15a 00000000 03004900 49004900 ..N…ñZ……I.I.I.’
SELECT REPLACE(SUBSTRING(@Value,CHARINDEX(‘:’,@Value)+1,CHARINDEX(‘†’,@Value)-CHARINDEX(‘:’,@Value)),’†’,”)
Not sure if this is a 2012 thing or what. FWIW, the database is in Full recovery model.
DECLARE @Value sysname = ‘0000000000000000: 08004e1f 0000f15a 00000000 03004900 49004900 ..N…ñZ……I.I.I.’
SELECT REPLACE(SUBSTRING(@Value,CHARINDEX(‘:’,@Value)+1,CHARINDEX(‘†’,@Value)-CHARINDEX(‘:’,@Value)),’†’,”)
Some of the substring function got truncated.
Hi buddy,
Normally, with the default collation, data is in between ‘:’ and ‘†’ sign. Due to this reason, I wrote code like this.
But, in your case there is no ‘†’ sign. Can you please give me you SQL server details along with collation
It should be like this, then it will work.
DECLARE @Value sysname
Set @Value = ‘0000000000000000: 08004e1f 0000f15a 00000000 03004900 49004900 †.N…ñZ……I.I.I.’
SELECT REPLACE(SUBSTRING(@Value,CHARINDEX(‘:’,@Value)+1,CHARINDEX(‘†’,@Value)-CHARINDEX(‘:’,@Value)),’†’,”)
Thank you.
Imran
The collation is SQL_Latin1_General_CP1_CI_AS and Language is English (United States).
SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘Edition’) AS Edition
Returns:
11.0.2100.60 RTM Developer Edition (64-bit)
Thanks a lot for the article Imran. I tested this script in my local DB and I added two filters to avoid the NULL Column names (In my table even I have 5 columns, I am getting addition NULL column and I am filtering it). It worked for me.
Hi,
Can you please post the modified lines of code only.
Thanks,
Imran
CPU utilization is peaking at 100%, is this normal?
Hi Oliver,
It is same like any other stored procedure in SQL Server. It may reach to 100%.
Thank you
Imran
[…] 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 […]
Thanks Muhammad Imran for the hard work, but i have a real problem i just realize that if you alter the table the Script does not work, i don’t know if you already knew that, but what can i do? the store script works on non-modified tables after the delete statement, thanks in advance.
Hi Kelvin,
Thank you for your feedback.
I know about this issue. And the reason behind this issue is when we decode SQL SERVER log, it requires the same columns of table once it was deleted and when it does not find the same number of columns, it returns error.
I am working on this issue as well. Will update you soon.
Imran
But the data still there?
I forgot to tell you, i just remove the identity of a column, the table hava the same columns, but one of then is not identity anymore.
Hi Kelvin,
Whenever, we remove identity from any column of the table , SQL drops that object and create it again also lost the log.
And In this case, Unfortunately you cannot recover the data from this procedure.
Imran
Hi Imran ,
I deleted some records from database.After that I used ‘Commit’ also. Can I recover those records ? Please reply to my query .
Hi Imran, I deleted some records from database. After that I used ‘Commit’ also. Can I recover those deleted records ? Please reply
Hi Reshmi,
Yes , In the case of commit, you can also recover the deleted data. I did a little modification in the procedure. Please try now.
Thank you
Imran
Dear Imran,
I am facing the problem with the procedure. It is taking long time to execute. when I use the following sub-query it takes a long time.
Select * 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.TestTable))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
AND SUBSTRING([RowLog Contents 0], 1, 1)In (‘0×30′,’0×70’)
Hi Savio,
Its speed depends upon the log size.
Regards,
Imran
[…] https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ […]
Muhamad,
you are a star!!
Hi Muhammad,
Great article, it works fine but in my case i didn’t recover data from this.
Thanks
Hi Prakash,
Please check your log (Select * from fn_dblog). If data exists in your log, this procedure should return the recovered data.
Thank you.
Imran
Imran rocks 🙂
Hi Imran,
After running this stored procedure, the log would be deleted?
Thank you,
Sirirat
Hi Sirirat,
When you run this procedure sometimes it looks as if that due to this stored procedure the records has been deleted from the log.
But actually SQL server keeps the log of each transaction but if your log size is small or if your database recovery model is in “simple”. Then SQL keeps the recent transactions records only.
Thank you.
Imran
mr Imran.. how to restore deleted data from archived transaction logs ? thank you, your site is very helpful.
what i’m trying to do is to restore deleted data of yesterday action. and i dont want to take effort by restoring database. I did execute the sp from this article , and able to take deleted data, but then the next day, i’ll try to execute again, but there is no data in the log. do you have suggestion for this base on your experience maybe. thank you.
Hi Ben,
Thank you for your feedback. This procedure does the recovery on the basis of log data if the data is available in the log it will return. But, as you mentioned you can recover it yesterday but not today. So if your log size is restricted to grow after certain size or may be your recovery model is simple , In any case SQL Server only keeps the recent records as per the log size.
Imran
If there are offline log backups you can use fn_dump_dblog to access them instead of fn_dblog.
To find out about it, try sp_help
sp_help fn_dump_dblog
Hi Imran,
Thanks for you super and fruitfull post.
Please give me you Facebook id so that I can follow your post.
Best regards
Viki
From Germany
Hi Viki,
Thank you for your feedback. My account ID is mimran18@gmail.com. Also, you can follow the post from this blog as well.
Imran
Imran,
I have started the procedure 3 hrs back and its still running for the 2000 records i deleted from one table.
Hi Deepak,
The performance of this procedure depends upon three criteria.
1-How many number of column in that table?
2-How many number of records to recover?
3-How many records do you have in the log?
Normally, it takes few seconds per record.
Imran
Thank you for your reply Imran,
The table has close to 100k rows but i have deleted only 2000 of them, how can i check the number of records in log?
To find the number of records in the log : Select * from fn_dblog(NULL,NULL).
If you have the backup you can recover it through this method also.
https://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
Also, please check the Collation of your database base.
The log file is having half million records, and to my bad luck i don’t have back up of database.
It does not require the latest backup.
I have a back up which i took immediately after i deleted the rows!
Did you take the full back up and transactional backup also ?
No just the full back up!
Please take a transactional Backup also. follow the step I mentioned in the another article, you will get your data.
And I am sure your remember the time of deletion as well. So you need restore the database state just before the deletion time.
i will try to do that. Thank you for your replies!
Most Welcome, First try the same on test database then you can do it on live database.
Thank you
The only question i have is in your example you have taken backups before deleting the records but in my case its after deleting the records does it work for this case?
I just now tested your case, I deleted few records from table after that took full and transaction backup but remember the time of the deletion. Just restore before the deletion time.
Thankyou!!!
Welcome
Imran,
I want to Create a job which backs up my transaction log for every hour and then at end of the day it backs up whole transaction log and deletes the hourly backup. Could you help on how to achieve this.
Hi Deepak,
Here is the solution :
http://msdn.microsoft.com/en-us/library/vstudio/hh561429.aspx
Regards,
Imran
Thank you for your reply Imran,
I am able to create jobs for hourly and Daily backups but how do i delete the hourly Backups when i create a total backup?
Welcome. Which sql server version are you using ?
SQL server 2008 R2
Thank you, will update you the solution shortly.
Hi Deepak,
I think, given below link can resolve your query.
http://social.msdn.microsoft.com/Forums/is/sqlsetupandupgrade/thread/b761399e-8a1e-489b-ba53-d3391a50b086
http://dba.stackexchange.com/questions/7468/how-to-delete-a-backup-from-a-backup-set-in-sql-server-2008-r2
Imran
Muhammad,
Works like a charm. Amazing work. Thank you very much.
Great Work !! I got solution for deleted record today…
Hi Imran, I ran your script on SQL Server 2008 R2 ” Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ” Getting following error message
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Any advice. Thanks. Loved your article
Hi Pranav,
Thanks. Please check your database compatibility, it should be more than equal to 90.
Also, you can comment from line # 30 -34.
Imran
hi imram
i am not getting the output for already deleted records
i am getting the output only when i delete the record after creating the procedure
please help
Hi Raj,
It returns the records from log , I think you donot have old records in log. Plz try the procedure without passing the date parameter.
Thanks
Imran
Exelent Code !
i just whiped up a database / table and filled it with temp data,
erases it all, and recovered it, using your code in ssms .
but.!
i dont know if i have missed something ( having skiped most of the comments)
but do i have to Cut and paste the result, to an “edit top 200” view in ssms to fully recover the data ?
i would like to be able to just run the procedure and my data is back as had i never been deleted,
any few lines i need to add to the sp to make data reapear in my table ?
The menthioned method has a flaw as decimal values at entered wrong.
i user a “,” to separate integer part from decimal, but the returned result in ssms uses “.” for this, witch means my data gets multiplyed by 10^n resulting in very bad data ( n meaning a integer in teh order of 3 to 6)
hello again Mohammad
I made it !
i altered your sp to just insert recovered data into a new table from whare i can easily reinsert into the original table keeping identity values and and so on.
i just affed ” into MYNEWTABLE ” into the last @SQL statement like this
SET @sql = ‘SELECT ‘ + @FieldName + ‘ into MYNEWTABLE FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘)) AS pvt’
thanks again This is great keep up the good work..
To honnor your work:, moved your comment into the code as it wont apeat in the sp after beeing created:
´– Script Name: Recover_Deleted_Data_Proc
— Script Type : Recovery Procedure
— Develop By: Muhammad Imran
— Date Created: 15 Oct 2011
— Modify Date: 22 Aug 2012
— Version : 3.1
— Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS.
Hi Per,
Thanks for your valuable feedback. Will incorporate it in the code.
Imran
Amazing, really thanks for this post, very usefull.
Greetings from Colombia.
I’m getting a database compatibilityerror and mine is set to 100 any ideas?
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Hi Chuck,
Please comment line # 30 till 34 and try again.
Imran
Really help full
Thanks for nice article
I do not want to recover all records deleted b/w a time. Running this command is returning all data in Hex form. How can I convert it back to readable table form..??
I need to recover records those satisfy a WHERE clause..??
Please help :p
Hi Shashwat,
Thank you. If you would like to recover your data from a deleted statement with where clause, first run given below script and note the date of the deleted statement.
SELECT qt.TEXT AS [Queries],*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
Reference : https://raresql.com/2013/01/30/sql-server-how-to-recover-the-recent-executed-query/
After that pass the date to this recovery procedure and it will recover your records.
Imran
Very-2 thanks Muhammad Imran
I find out deleted Data
Hello Muhammad, great script. Congratulations on your great work.
I’m trying to understand the way the script works but I’m having trouble in figuring out where did you get the information for the value “30000” and the tranformation “- POWER(2, 15)”. I was able to understand (I think) that this is related with LOBs and the “Row-Overflow Data” case. Can you point to some resources where I might find the reasoning behind those values? Thank you very much.
Ok… I have already answered most of my own question. Still, there is something that I’m curious about: Why 30000 and not, for instance, 31000? Is there any especific reason? Thank you.
Hi Hugo,
Thank you for your feedback, This 30000 condition I put it for BLOB data types, but mostly they are more than 32000, So you can revise the condition like more than 32000 as well.
Imran
Just a heads up in case someone here didn’t notice this small problem. The function fn_dblog returns “RowLog Contents 0” and all the others like it as a NVARBINARY(8000). This means that the content from a page can be truncated because this content can be something like 8054 bytes long. In this case, mainly in LOBs, there is nothing to do…
Hi Hugo,
Agreed. Due to this restriction, SQL does not keep the deleted data in “RowLog Contents 0” if it is more than 8000 characters. It keeps the data in data pages and keeps the addresses Only in “RowLog Contents 0”.
Due to this most of the LOBs data are in pages and its addresses are in “RowLog Contents 0”.
Thanks
Imran
Hi Muhammad,
Thank you for your answer. Yes, I notice that. The thing is that when we delete a row that has a part of a LOB in it, if it is the last part of the LOB (the one that has the adresses to all the other LOB pages) the Data part is destroid almost immediately. This means that we cannot retrieve the data from the page. Of course that same data is kept on the log record with the context “LCX_TEXT_MIX”, so you can retrieve the data that way (this data could be bigger that 8000 bytes, but it’s very unlikely).
The same doesn’t happen to pages that are not the last LOB page. If we do a insert after the delete, it’s very likely that those pages are going to be rewritten. In this case, we could get the information from the insert/updates records (Full mode). And it’s here where the problem arises. While we could, in theory, recover all information in a DB that is in Full mode, in reality that it’s not possible due to this restriction. That was, basically, the point I was trying to make. Also, it might be a good idea to compare the LSN from the Pages to make sure that those pages indeed contain the information of that row. To my knowledge there is no other way to make sure that those pages were not rewritten. Beyond, of course, checking if there is a log record, later in the log, that changes that page. But because, in my strategy I try to read the log only once. that option is off the table :-).
Aagin, thank you for all your great work.
Hi Imraan,
I have executed ur proc but it giving me error like:
(8 row(s) affected)
Msg 229, Level 14, State 5, Procedure Recover_Deleted_Data_Proc, Line 68
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria.
Can you help me in this regard.
Hi Jyotsna,
You don’t have sufficient privileges in your SQL Server. Please get the privilege and execute it again.
Imran
Ok thanks Imran….
Hi Imran, great post.
But I’m having some problems while trying to restore data using your sp
When I run
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE
AllocUnitName = ‘dbo.tb_detalle_contenedores’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’)
AND Operation = ‘LOP_DELETE_ROWS’
It gives me 6809 rows. I delete about 6600 rows, so I understant the data is there. I have extract that data in an excel file just in case and i have inserted in another table as well.
The structure of the table I’ve just delete is as follows
tablename: tb_detalle_contenedores
columns:
CODIGO int null,
CODIGO_INSPECTOR int null
NUM_CONTENEDOR varchar(200) null
Estado int null
SQL SERVER 2005 9.0.1399.06
COLLATION Modern_Spanish_CI_AI
When I execute the SP (I changed the time format to ‘DD/MM/YYYY’ otherwise it gives me a related error) I shows this error:
(8 filas afectadas)
(0 filas afectadas)
Mens 50000, Nivel 16, Estado 1, Procedimiento Recover_Deleted_Data_Proc, Línea 180
There is no data in the log as per the search criteria
I have to say that when I check the columns “Begin Time” and “End Time” on sys.fn_dblog, for the records I retrieve on the simple select, it shows null values, although “RowLog Contents 0” shows data.
Please, help me on this (I hope you can see this post soon enough)
Renato
I found out that the issue with the error was on my own post. The null values on Begin Time. I comment the lines where the SP was asking for this column.
At the moment the SP is running (hopefully running good) 40 mins so far.
I’ll post the results here 🙂
This rise a question. Why does not register the Begin Time and End Time on fn_dblog?
Could it be that in fact, there is no data and the actual SP is running for nothing? *shivers*
I forgot to mention that the database is on full mode recovery.
So far… 4 hours 20 minutes…
I’m crossing fingers on this…
I’ll have to stay overnight at work checking this.
My… so far 6 hours 44 minutes…
Is it normal to take this long? or is it looping or something?
I’m not sure what to do at this point.
I think there is something wrong it should not take that much time. Please send me a sample[ row log 0] content?
I have just send you some rows of the result from the query
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE
AllocUnitName = ‘dbo.tb_detalle_contenedores’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’)
AND Operation = ‘LOP_DELETE_ROWS’
The SP is still running. I was thinking maybe I did something wrong by commenting the lines that were asking for the date intervals?
I have the modified SP, just in case I’m going to send it to you as well.
Thanks for the help, I’m still at work 🙂
Thank you so much Muhammad, you saved my life! Great article. Great job.
Keep it up 😀
Hi Muhammad
Thanks a million, this is pure genius! Saves my life
hi,
we have tried your script but it’s taking too long. It has been 19 hours since we ran it, is it suppose to be this way? the log file size is more than 1GB and records to be retrieve is more than 60k..
Thanks
Hi Joie,
It should not be in this way. Please send me the structure of your table along with SQl Server environment details.
Thanks,
Imran
Thanks Imran, really appreciate the response. I have send the table structure to your gmail.. the db is in SQL server 2005 running in Windows Server 2008 R2..
Thanks
[…] Die Möglichkeiten das log entsprechend zu interpretieren ist in diesem Blog sehr gut erklärt. […]
Hola Himrad. Muchas gracias por tu apoyo, esto salvo el dia y por mucho salvo a todo el departamento de TI. Sigue asi compañero !!!! 😀
Hi Himrad. Thanks a lot. Its save my day and the day of all IT department.
Hi, I use the command Delete from table, and the only result I can see when I check the log is the operation LOP_DELETE_SPLIT. Do you know if I can do something with that?
Hi,
Please copy the data column [AllocUnitID], [Transaction ID],[RowLog Contents 0] from log into excel and send me some sample data from it.
Also send me the table script and SQL Server environment.
Regards,
Imran
Hello. Thank you for this procedure. How long should I expect it to take? Minutes? Hours?
I ran Select * from fn_dblog(NULL,NULL) and it returns about 38000 records.
Also, if I use a smaller date range, should I expect it to go faster or does it still have to process every log entry.
Hi Tamara,
It will take some time for 38K records may be hours.
You are right if your reduce the date range it will recover quickly.
Imran
I did try one thing for testing, I deleted around 75000 rows from another table. And now i am getting the rows by running the same command given by you…But can i recover the actual data from this log…
As long as the data available in the log it can recover any number of records.
will it work for sql server 2008
Hi Jay,
It definitely works for SQL Server 2008 as well.
Kindly comment line # 30 till 34.
Thank you
Imran
Thank you very much Imran.
It worked, but I am getting the error when i am passing the dates to stored procedure.
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 159
There is no data in the log as per the search criteria
Hi Jay,
What date range did you pass in the stored procedure ?
Imran
First I deleted the data for ‘2012-07-24’ from one of the tables.
Then I gave this command.
EXEC Recover_Deleted_Data_Proc ‘RGFINAL’,’dbo.SalInvMain’,’2012-07-24′,’2012-07-24′
When did you delete the data ?
Thanks a lot.
And one more question, how long will it take to restore 130824 rows.
Welcome, It depends upon the structure of the table.
delete from SalInvMain where VouDate = ‘2012-07-24 00:00:00.000’..
Just before executing the stored procedure…
You need to pass the date and time when you deleted the data.
Lets say if you deleted the data today, you must execute it like this (with today’s date only).
EXEC Recover_Deleted_Data_Proc ‘test’,’dbo.Test_Table’ ,’2013/06/11′,’2013/06/11′
EXEC Recover_Deleted_Data_Proc ‘RGFINAL’,’dbo.SalInvMain’,’2012-04-01 00:00:00.000′,’2013-03-31 00:00:00.000′
Go
I am getting the same error..
What is your database recovery model ?
simple
In this case chances are very rare to recover the deleted data.
However, can you please check given below query result set that it returns how many records ?
Select * from fn_dblog(NULL,NULL)
105458 row(s) by running Select * from fn_dblog(NULL,NULL).
Jay, DO NOT restart the server, or DO NOT pass any transaction in the server. Let me give you a query to check that the data of your table is available or not.
Do you have backup ?
I have backup till yesterday’s date. Its today that I have deleted the data in office. Now am looking for a solution so that i can apply that in office tomorrow.
Can i recover the actual data by running this command.
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName Like ‘%SalOrdDelItem%’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
So that tomorrow, i will try to recover actual data from the log with the help of above command.
Jay, this query is just to test that data, that it is available in the log or not. After that you need to run the stored procedure to recover the data.
Is there any other way i can recover data, because it is a live client database.
I m getting 105458 rows by running the command given.
Select * from fn_dblog(NULL,NULL)
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName Like ‘%yourtablename%’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
Please change your table name in this query,try and let me know the result set. But it should be applied on live database where you have deleted the records.
Thanks..
I ran the query..
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName Like ‘SalInvMain’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
But i am getting 0 rows..
It Should be With % sign. WHERE AllocUnitName Like ‘%SalInvMain%’
I am getting 542 rows with one column, but i have deleted more than 6000 rows.
Unfortunately, we can recover only 542 records due to simple recovery model.
Okay..
I will restore yesterdays database first and then i will try to recover if its possible, Otherwise i will make around 100 new entries..
I really appreciate your help..
Most Welcome
Can i recover the actual data by running this command.
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName Like ‘%SalOrdDelItem%’
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
So that tomorrow, i will try to recover actual data from the log with the help of above command.
Sorry one more thing, which recovery mode should i set for future purpose..
It must be full and it requires proper maintenance as well.
maintenance ???
Yes, Because the log file will grow very fast so you should take the different types back up on regular basis.
Okay, thanks..
I never expected that i will get instant help..
Welcome, anytime
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
Hi Muhammad,
Fantastic Article:
Hi Imran,
I have used the procedure to recover 300 rows with the size of 1.8GB mdf file & 2.6 GB of ldf file. It took 16 Hours for the recovery & the data is 100% accurate. Thanks a lot for sharing this. Great job.
Really Helpful.. Thanks a lot for sharing.
– Mruthunjaya J
Hi Muhammad i ve tried ur code on local and server data is been recovered,
some days before 3000 records are been deleted from me and i ruin ur code
after 4 hrs it gave me error of
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
plz, give me feedback to get out of this error….
Hi,
It says your client connection has been disconnect from server. Please connect and execute again.
thanks for ur feedback,
it takes very long time for executing to fetch 3000 records on server
and it has taken 20 hrs but no result still it is executing
Hi Padmaraj,
Can you please share your table structure, So that I can modify the script for you to reduce the recovery time.
Thank you
Imran
i have used from date as 2013-07-31 and to date as 2013-07-31
is it valid
Yes, this is valid date. But it will recover for one day only.
Hello Sir!
Thanks for reply,
Yesterday i executed recovery procedure it took about 24 hrs above and at last gave message as
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
Below is my table structure, plz help me for script to reduce recovery time
USE [DB_nss]
GO
/****** Object: Table [dbo].[Applicant] Script Date: 08/08/2013 23:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Applicant](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[appName] [nvarchar](60) NULL,
[appRollNo] [bigint] NULL,
[appClass] [nvarchar](15) NULL,
[appdivision] [nvarchar](2) NULL,
[appDate] [date] NULL,
[usrUserId] [nvarchar](50) NULL,
[appCollgeCode] [nvarchar](10) NOT NULL,
[appStudCode] [nvarchar](10) NOT NULL,
[appYear] [int] NOT NULL,
[appDegree] [nvarchar](30) NULL,
[appImage] [image] NULL,
[appPersonalMobNo] [nvarchar](12) NULL,
[AdminId] [nvarchar](36) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Hi,
You have BLOB data type in your table so you need to run the complete stored procedure.
But if you can copy the records from SQL log (using fn_dblog) and paste into excel and send it to me.
Will try to recover the data and send it back to you.
Imran
Happy Id 2 u Sir!
Dear Imran ji when i try to run “Recover_Deleted_Data_Proc ” to recover deleted deta from server but show s an error
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Hi Vikash,
You don’t have the privilege to run fn_dblog function. Kindly add the access in your user account and then execute again.
Imran
Thanks for reply
after searching a lot i found that my sql server 2008 data was truncated, now is any idea or solution to recovered truncated data please replay ASAP
Thank You.
Hi Vikash,
The best solution is to recover from the backup. But if you don’t have backup please try give below solution.
https://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/
Imran
Hi Imran,
How r u?
I have scenario where need to display past 7 days on the basis of parameter
like
— here the procedure to display past 7 days
Usp_Display_Date ‘2014-11-30’
— result should be like this
2014-11-23
2014-11-16
2014-11-09
2014-11-02
2014-10-26
2014-10-19
2014-10-12
— here 30-11-2014 is Sunday so display last 7 sunday
if i pass parameter is ”2014-11-29
— result should be like this
2014-11-22
2014-11-15
2014-11-08
2014-11-01
2014-10-25
2014-10-18
2014-10-11
— here 29-11-2014 is Saturday so display last 7 Saturday
please look into it asap
Thanks
how can i send an excel file to u
Send it to mimran18@gmail.com
I had Send an excel file of fn_dblog with sample data
Hi, Please send me your SQL Server details.
Thank you
Imran
Hi Mohammed,
will the transaction log get overwritten if I run the procedure multiple time and get error message?
I get this error message:
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
later i resolved this and tried. But got a error as there is no value in the log. Please help Mohammed.
any help would be helpful.
Hi Joshi,
Please check your SQL Server recovery model and let me know. It should be FULL
Thanks
Imran
If a backup has occurred then fn_dump_dblog can be used instead of fn_dblog to collect the transactions that have moved to the log backup or the database backup.
Hi Imran,
How do I recover if its in FULL recovery SQL Mode? Please advice.
thanks,
joshi
Hi Imran,
my earlier transaction logs are erased. Is there any way I can recover my transaction logs? Please let me know. Your help is very much apprecaited.
Thanks,
Joshi
Hi Joshi,
Do you have the database backup also please specify your database recovery model.
Thanks,
Imran
Hi Imran,
The recovery model is ‘Simple’. Now I have changed to FULL. Will the affect previous transaction logs? Please advice.
thanks,
Joshi
Hi Imran,
Further more, when I use third party software APEXSql Recover to retrieve it from logs, it uses ‘Online’ Transaction log’ and recovers me all the records.
The recovery model is ‘Simple’. Now I have changed to FULL. Will the affect previous transaction logs? Please advice.
thanks,
Joshi
Hi Mohammed,
I just realized, there is an another way to do this. And I am sharing so that it will help others.
We can use Toad for SQL Sever and use its log reader feature.
I tried with the above stored procedure, but the problem was my fn_dblog got restored some how. Or it only showed me logs of current date. Neither the fn_dump_log showed me transactions.
I have done so much research on this and I can help people who will need any help during problems like this.
You can reach me at avjk30@gmail.com
thanks a lot,
Joshi
I am trying to use this script but it will not find the table I am looking to recover. I am sure this table exists as it predates my time here dbo.invoiceheader.
SELECT count(*) FROM INFORMATION_SCHEMA.TABLES
WHERE [TABLE_SCHEMA]+’.’+[TABLE_NAME]= ‘dbo.invoiceheader’
returns a count of 1. Any help is appreciated.
Hi,
Did you delete the records from table or did you drop the table ?
Imran
Deleted the records, the script doesn’t seem to recognize the table I am attempting to recover from as even existing.
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 28
Could not found the table in the defined database
Thanks
Please change the database name in the procedure.
Still receiving the same error for statement
EXEC Recover_Deleted_Data_Proc ‘clmiqa2′,’dbo.invoiceheader’
I have tried
SET @database_name=’clmiqa2′
in the procedure code
and get the same error.
I am checking the table existence at line #36 in the stored procedure. Kindly use the give below query to make sure that you have the table in that particular database.
Use clmiqa2
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+’.’+[TABLE_NAME]=’dbo.Invoiceheader’
Thanks
Imran
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
CLMIQA2 dbo invoiceheader BASE TABLE
Is your database case sensitive ?
No, the collation is not case sensitive.
SQL_Latin1_General_CP1_CI_AS
Deleted Records from table by firing sql query delete from Applicant
________________________________
WordPress.com NotGreenDBA commented: “No, the collation is not case sensitive. SQL_Latin1_General_CP1_CI_AS”
In this case, I think the Transaction name will be different. It will not be “[Transaction Name] In (‘DELETE’,’user_transaction’)”
Can you please check the transaction Name ?
Thanks
Imran
you saved my life 🙂 thanks a million 🙂
Hi Muhammad… Say about what? It works too slowly, but it’s excellent.
Cheer… And cheers for you!
hi, please help me
Msg 207, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 544
Invalid column name ‘FILE IDS’.
Msg 207, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 524
Invalid column name ‘FILE IDS’.
Msg 207, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 548
Invalid column name ‘LINK ID’.
so far 7 hours 44 minutes…
Is it normal to take this long? or is it looping or something?
It depends upon how much data, do you want to recover.
Imran
hi muhammad, mdf data 450mb ldf data is 493GB and recovery table 50K+ rows inside. do you think recovery rows ? spend 12 hour and still waiitng
Hi,
Is there any BLOB data type in your table ?
Imran
yes, 850+ hex data showing. but process running 19 hours ago
Ya, then it will take longer time to recover.
Imran
great article very thanks. i will still waiting complete to procedure
Most Welcome, There is other work around also. Please let me know if you do not get your deleted data.
Thanks
Imran
36 hours ago and still waiiting. :((
i try with 1 row of data in the table, delete it and run this proc, and it take longer than 30 min….is there any issue?
Hi Brandon,
It generally takes 2-3 minutes for few records. Kindly post your SQL Server environment details. So that I can test it and update you accordingly.
Thanks,
Imran
Hello Imran Ji,
Assalamu Alaikkum. Alhamthulillah Very Nice Article. I have tried this Sp and its working fine for me. But when i restart the sql services then i tried , That time it was not working .
I have checked data in fn_dblog tables. There is no entries found.
I just want to know what’s the scope of fn_dblog table(I mean how long that table hold the log entries)
Also if possible, please mention what are all the data types it won’t work.
Thanks,
Mohamed. A
Hi Abdul,
Walikum Salam, Once you restart SQL server, it will remove the committed transactions from the SQL Server log.
Fn_dblog table (SQL Server log) depends upon the size of the log file defined in the database. If the log size is small it will maintain only the recent log. It also depends upon the recovery model.
It works for all data types available in SQL Server 2005.
Thanks,
Imran
Hi Muhammad,
I was hoping you could help with an error I am getting with your script. I am using the sample data you have posted here. I get an Invalid length parameter passed to the LEFT or SUBSTRING function. I narrowed it down to the following part of the script:
SELECT [Page ID],
[FILE IDS],
[PAGE IDS],
Substring([ParentObject],CHARINDEX(‘Slot’, [ParentObject])+4, (CHARINDEX(‘Offset’, [ParentObject])-(CHARINDEX(‘Slot’, [ParentObject])+4))-2 ) as [Slot ID],
[AllocUnitId],
Substring((
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(‘:’,[Value])+1,CHARINDEX(‘†’,[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%’ Order By ‘0x’+ LEFT([Value],CHARINDEX(‘:’,[Value])-1)
FOR XML PATH(”) ),1,1,”) ,’ ‘,”)
),1,20000) AS [Value]
–Substring((
— SELECT ‘0x’ +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%’ Order By ‘0x’+ LEFT([Value],CHARINDEX(‘:’,[Value])-1)
— FOR XML PATH(”) ),1,1,”) ,’ ‘,”)
— ),7,4) AS [Length]
From @pagedata B
Where [Object] Like ‘%Memory Dump%’
Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]–,[Current LSN]
Order By [Slot ID]
Appreciate any help you can provide.
Hello Muhammad Imran,
First of all, congratulations for this amazing SQL work you did..
I’m having a problem and don’t know the reason.
I run the SP and it is executed for more than 1hour and never stops..
I really need to recover deleted data from one table (about 300 records) and I tried all the things I know and that I found and this is my last (and best) choice.
Hope you can help me.
Than you,
Valada
Hi Valada,
Thank you for your feedback. Do not worry about the data, I can recover it, if the data is available in your SQL Server log.
Please send me table structure and your SQL Server environment details.
Thanks,
Imran
Hi Imran,
Thanks a lot for your fast reply.
The structure of my table is very simple:
TABLE [dbo].[OSUSR_HFZ_CONTACT] :
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](100) NULL,
[JOBTITLE] [nvarchar](50) NULL,
[PHONE] [nvarchar](20) NULL,
[MOBILE] [nvarchar](20) NULL,
[EMAIL] [nvarchar](250) NULL,
[COMPANYID] [int] NULL,
[SECONDARYEMAIL] [nvarchar](250) NULL,
CONSTRAINT [OSPRK_OSUSR_HFZ_CONTACT] PRIMARY KEY CLUSTERED
The SQL Server details:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Really hope you can help me.
Thank you,
Valada
Hi Valad,
Please stop the procedure and execute the given below query and let me know if you can get some data.
–Use databasename
Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE
AllocUnitName Like ‘%OSUSR_HFZ_CONTACT%’
Imran
Hi Imran,
I tried that but the same is happening, ie, the query execution seems not to stop. It’s running for more than 10min and continues 😦
Thank you,
Valada
Hi Valada,
It is a good sign, it means you have the data in the log.
Wait for sometime, once you have the data, please copy and paste it into excel and send it to me.
Imran
Hi Valada,
Please revise the query.
–Use databasename
Select *
FROM sys.fn_dblog(NULL,NULL)
WHERE
AllocUnitName Like ‘%OSUSR_HFZ_CONTACT%’
Hi Imran,
I tried that new version but the same is happening, ie, the query execution seems not to stop. It’s running for more than 2hours and continues 😦
However, this time I started to get some results..More than 2000..but now my connection failed and I have to start all over again.
What are the next steps to take, since it seems that I have that in the logs.
Thank you,
Valada
Hi Valada,
Once you get the data from the log, you need to copy and paste the data into excel and send it to me. (If data is not confidential).
I will check why it is taking so long. And will recover your data (if possible, depends upon the log condition)
Thanks,
Imran
Hi Imran,
I tried to send you an email to your gmail account, a few hours ago.
Let me know if you could receive it.
Thank you,
Valada
When I execute the Recover_Deleted_Data_Proc
show this message
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
When I make mistake
Kire Mrceski
Hi Kire, what is your SQL server version ?
Imran
Hi Muhammad,
Great article!
I am hoping you can help me recover some data. I have tried your script and get the following output:
(8 row(s) affected)
(3064 row(s) affected)
(18384 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(46 row(s) affected)
(46 row(s) affected)
(46 row(s) affected)
(447 row(s) affected)
(447 row(s) affected)
Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 511
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(18384 row(s) affected)
(3064 row(s) affected)
———————–
My table has the following schema:
CREATE TABLE [dbo].[Resources_tbl](
[ID] [int] IDENTITY(1,1) NOT NULL,
[App] [nvarchar](50) NULL,
[Tenant] [int] NULL,
[Culture] [nvarchar](10) NULL,
[Key] [nvarchar](50) NOT NULL,
[Value] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Resources_tbl] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY],
CONSTRAINT [UN_Resources_tbl] UNIQUE NONCLUSTERED
(
[App] ASC,
[Tenant] ASC,
[Culture] ASC,
[Key] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
If I run the following command I get 3064 rows which is about the number of rows that were deleted:
Select [RowLog Contents 0] 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.Resources_tbl’ + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
AND SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
How do you recommend I proceed?
Many thanks,
Matt Grogan
Hi Matt,
Thanks for the update. Kindly rewrite the query as mentioned below and save the result in excel. In addition to this please send me SQL Server environment details.
Select * 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.Resources_tbl’ + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
AND SUBSTRING([RowLog Contents 0], 1, 1)In (0×30,0×70)
Imran
Thanks for your help – you’ve saved my blushes.
Good day Muhammad,
I have this following error :
Msg 210, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 637
Conversion failed when converting datetime from binary/varbinary string.
Hi Lulamile,
Thanks for your update. With is error, it is little difficult to give you the solution.
In order to fix this issue, can I get some sample data ?
Thanks,
Imran
This is my table schema
CREATE TABLE [dbo].[MatchedTripsNoGABS](
[vRouteNumber] [varchar](5) NULL,
[vTripOrigin] [varchar](255) NULL,
[vTripDestination] [varchar](255) NULL,
[fTripKms] [float] NULL,
[iVehicleID] [int] NULL,
[vVehicleDescription] [varchar](255) NULL,
[vVehicleRegNo] [varchar](20) NULL,
[dDate] [date] NULL,
[tTripScheduledDepartureTime] [datetime] NULL,
[dtBusDepartureTime] [datetime] NULL,
[tTripScheduledArrivalTime] [datetime] NULL,
[dtBusArrivalTime] [datetime] NULL,
[iTripSequenceID] [int] NULL,
[iServiceID] [int] NULL,
[vDayOfWeek] [varchar](255) NULL,
[iTimeTollerance] [int] NULL,
[uLastUpdatedBy] [varchar](250) NULL,
[dtLastUpdated] [datetime] NULL,
[vComment] [varchar](100) NULL,
[bSubsidised] [bit] NULL,
[iMisMatchReasonID] [int] NULL,
[vMisMatchReason] [varchar](255) NULL,
[vCompliancy] [varchar](7) NULL
) ON [PRIMARY]
GO
Sample data and SQL Server details please.
Thanks
Imran
Hi Imran,
Absolutely fantastic work on this!
Cheers!
Tom
Thank you Mohamed , Worked Perfectly
Thank life savior
I need to get the transaction sid, when I insert it into a table as nvarchar(50) it shows strange ascii characters.
Hi Scott,
Can you please elaborate ?
Imran
Nice work. Thank you very much.
In my tests, I got a positive result.
However, before I scan the apex sql did not get back deleted records in a table.
Apex Sql deleted data shows, recover_deleted_data_proc does not show.
‘There is no data in the log as per the search criteria’
Can you help.
Hi Hailt,
There are different methodologies, different companies are using to recover the deleted data. I recover it through the SQL Server log file. However, if you can provide sample table along with the data, I can test and fix it (if required) accordingly.
Many thanks,
Imran
This is fantastic work done. Thanks Muhammad a lot.
Hi Imran, it works! Fantastic job! Thanks for this huge help in our case.
Thanks for Great help to fix and tune up the code by customizing the proc. Great to have results. Very Nice Job!
Hi Dani,
I am wondering could you recover the blob type data, such as text and image ?
Using this script, I could recover common data type data correctly instead on blob type.
Thanks
Wen Zhong
Is th
is Script worth if the SQL Service got restarted after deletion operation …??
Unfortunately, It will not work in this case.
Imran
When I executed the
EXEC Recover_Deleted_Data_Proc ‘test’,’dbo.Test_Table
I get the following output on my test server but the deleted record is never restored.
(8 row(s) affected)
(1 row(s) affected)
(32 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(445 row(s) affected)
(445 row(s) affected)
(445 row(s) affected)
(446 row(s) affected)
(446 row(s) affected)
(446 row(s) affected)
(446 row(s) affected)
(446 row(s) affected)
Hi Ray,
Is there any error ?
Imran
Yes, two errors.
.Net SqlClient Data Provider: Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 511
Invalid length parameter passed to the LEFT or SUBSTRING function.
.Net SqlClient Data Provider: Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 637
XML parsing: line 1, character 2, illegal xml character
Hi Muhammad,
I forgot to add that my test server is running SQL 2012 SP1.
Hello Muhammad,
Did you read my last two posts?
Muhammad,
If I am wasting my time here please let me know. I responded to your question but you have not replied,
Hi Ray,
I apologize for the late response. I tested some scenarios directly from SQL Server and it is working fine. However, when I tried to use it from any application outside the SSMS, it gives me this error.
I am still fixing this issue. Can you please help me to create the same environment in order to test it properly.
Thanks
Imran
Sure, how do you propose we do this?
Hi Ray,
Please provide me a sample table with sample data, which is generating error. I will create it on my database and test it.
In addition, Please give me sample dot net code which is creating problem also.
Thanks
Imran
I wanted to start from a clean slate, so I deleted my test database and created a new one. When I started to create the table and insert the record from your script I had no problem. However when I tried to create the procedure I got the following error.
.Net SqlClient Data Provider: Msg 3701, Level 11, State 5, Line 9
Cannot drop the procedure ‘Recover_Deleted_Data_Proc’, because it does not exist or you do not have permission.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
Incorrect syntax near ‘<'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 139
Incorrect syntax near '<'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 151
Incorrect syntax near ')'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 154
Incorrect syntax near ','.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 155
Incorrect syntax near ','.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 156
Incorrect syntax near ','.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 227
Incorrect syntax near '<'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 347
Incorrect syntax near '<'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 351
Incorrect syntax near '+'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 358
Incorrect syntax near '+'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 447
Incorrect syntax near '<'.
.Net SqlClient Data Provider: Msg 156, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 458
Incorrect syntax near the keyword 'GROUP'.
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure Recover_Deleted_Data_Proc, Line 469
Incorrect syntax near '<'.
.Net SqlClient Data Provider: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'Recover_Deleted_Data_Proc'.
.Net SqlClient Data Provider: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'Recover_Deleted_Data_Proc'.
I am logged in as sa, so I am sure it is not a permissions issue. I am not sure why this is happening, I had no problems creating this SP but it now appears that a new issue is preventing this from running.
Hi Ray,
I think the stored procedure is not copied properly from the page. Please copy and execute it again.
Even if you face problem after doing this. Please let me know will send you a copy of the procedure.
Thanks
Imran
Hi Muhammad, I am getting the same results, would you send me the file?
Thanks
Can I get latest script with all modifications? This one has couple of syntax error and requires lot of time to remove red lines.
Hi Amish,
Please copy the script and paste it into word document and again copy from word and paste it into SQL Server Management Studio.
Once you do it, it will be ok. Please let me know if it does not work. Unfortunately, it works like this in WordPress.
Thanks,
Imran
Hi imran in SQL 2008r2 i having data loss problem so pls give me query for this
Hi Bhaskar,
Is this script not working in SQL Server 2008R2 ?
Imran
Hi. when the script execute this part: SELECT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN (‘LCX_NULL’) AND Operation in (‘LOP_BEGIN_XACT’)
And [Transaction Name] In (‘DELETE’,’user_transaction’)
And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To
is taking a long time.
Is there something I can fix this?
Hi,
You can reduce the date range and it will reduce the time. However you can directly pass the transaction ID instead of this sub query.
Imran
Hello Imran, When I delete record from SSMS , it gives me result, however when I delete record from my application , it does not give any result. Can you help me?
Hi Amish,
Can you provide me sample log in order to understand it and can help you accordingly.
Imran
[…] 以下为译文：https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ […]
Hello Muhammad! Thanks for you script! It is very good. I am wondering, could you spare some time working on “How to recover inserted records from SQL Server without Backup”. Thank you very much!
hi dont understand how to use this code
i am new to this
Hi Imran,
I get the same errors as Ray.
I find the procedure could recover data well if the data types do not contan blob type. However, if the table contains blob type such as text, image. It will shows the following errors:
Msg 537, Level 16, State 3, Line 565
Invalid length parameter passed to the LEFT or SUBSTRING function.
I just use your sample test in this article.
My working environment is SQL Server 2012.
Hope you could answer my question.
Thanks!
Wen Zhong
I ran your script without changing anything but it is not working for me. Below is my table script & environment example.
**Script**
CREATE TABLE [dbo].[ProjectRecords](
[RecordId] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](100) NOT NULL,
[ImageId] [varchar](100) NOT NULL,
[Parish] [varchar](100) NULL,
[Surname] [varchar](100) NULL,
[MilitaryNumber] [varchar](100) NULL,
[Nationality] [varchar](100) NULL,
[DateOfBirthAndSpecialNumber] [varchar](100) NULL,
[FirstName] [varchar](100) NULL,
[BarnOrChild] [varchar](100) NULL,
[DateOfMarriage] [varchar](100) NULL,
[SpouseOrGuardianDateOfBirth] [varchar](100) NULL,
[Occupation] [varchar](100) NULL,
[BirthplaceParish] [varchar](100) NULL,
[BirthplaceCounty] [varchar](100) NULL,
[EarlierCensus1] [varchar](100) NULL,
[EarlierCensus1Parish] [varchar](100) NULL,
[EarlierCensus1County] [varchar](100) NULL,
[EarlierCensus1Place] [varchar](100) NULL,
[EarlierCensus2] [varchar](100) NULL,
[EarlierCensus2Parish] [varchar](100) NULL,
[EarlierCensus2County] [varchar](100) NULL,
[EarlierCensus2Place] [varchar](100) NULL,
[GuardianFirstNameInitials] [varchar](100) NULL,
[PostalAddressLivingplace] [varchar](100) NULL,
[PostalAddressCity] [varchar](100) NULL,
[SailorHouseNumber] [varchar](100) NULL,
[NumberOnImage] [varchar](100) NULL,
[EntryUserId] [varchar](100) NULL,
[EntryDate] [datetime] NULL,
[VerifyUserId] [varchar](100) NULL,
[VerifyDate] [datetime] NULL,
[WorkstationId] [varchar](100) NULL,
[ImageFocusPosition] [varchar](100) NULL,
[ProjectId] [varchar](100) NULL,
[ImageType] [varchar](100) NULL,
[NumberOnImageFocus] [varchar](100) NULL,
[SurnameFocus] [varchar](100) NULL,
[MilitaryNumberFocus] [varchar](100) NULL,
[NationalityFocus] [varchar](100) NULL,
[DateOfBirthAndSpecialNumberFocus] [varchar](100) NULL,
[FirstNameFocus] [varchar](100) NULL,
[BarnOrChildFocus] [varchar](100) NULL,
[DateOfMarriageFocus] [varchar](100) NULL,
[SpouseOrGuardianDateOfBirthFocus] [varchar](100) NULL,
[OccupationFocus] [varchar](100) NULL,
[BirthplaceParishFocus] [varchar](100) NULL,
[BirthplaceCountyFocus] [varchar](100) NULL,
[EarlierCensus1Focus] [varchar](100) NULL,
[EarlierCensus1ParishFocus] [varchar](100) NULL,
[EarlierCensus1CountyFocus] [varchar](100) NULL,
[EarlierCensus1PlaceFocus] [varchar](100) NULL,
[EarlierCensus2Focus] [varchar](100) NULL,
[EarlierCensus2ParishFocus] [varchar](100) NULL,
[EarlierCensus2CountyFocus] [varchar](100) NULL,
[EarlierCensus2PlaceFocus] [varchar](100) NULL,
[GuardianFirstNameInitialsFocus] [varchar](100) NULL,
[PostalAddressLivingplaceFocus] [varchar](100) NULL,
[PostalAddressCityFocus] [varchar](100) NULL,
[SailorHouseNumberFocus] [varchar](100) NULL,
[SerialNumber] [int] NULL,
[SoftwareVersion] [varchar](100) NULL,
[IsParishVerified] [bit] NULL,
[IsNumberOnImageVerified] [bit] NULL,
[IsSurnameVerified] [bit] NULL,
[IsMilitaryNumberVerified] [bit] NULL,
[IsNationalityVerified] [bit] NULL,
[IsDateOfBirthAndSpecialNumberVerified] [bit] NULL,
[IsFirstNameVerified] [bit] NULL,
[IsBarnOrChildVerified] [bit] NULL,
[IsDateOfMarriageVerified] [bit] NULL,
[IsSpouseOrGuardianDateOfBirthVerified] [bit] NULL,
[IsOccupationVerified] [bit] NULL,
[IsBirthplaceParishVerified] [bit] NULL,
[IsBirthplaceCountyVerified] [bit] NULL,
[IsEarlierCensus1Verified] [bit] NULL,
[IsEarlierCensus1ParishVerified] [bit] NULL,
[IsEarlierCensus1CountyVerified] [bit] NULL,
[IsEarlierCensus1PlaceVerified] [bit] NULL,
[IsEarlierCensus2Verified] [bit] NULL,
[IsEarlierCensus2ParishVerified] [bit] NULL,
[IsEarlierCensus2CountyVerified] [bit] NULL,
[IsEarlierCensus2PlaceVerified] [bit] NULL,
[IsGuardianFirstNameInitialsVerified] [bit] NULL,
[IsPostalAddressLivingplaceVerified] [bit] NULL,
[IsPostalAddressCityVerified] [bit] NULL,
[IsSailorHouseNumberVerified] [bit] NULL,
[OtherInformation] [varchar](100) NULL,
[OtherInformationFocus] [varchar](100) NULL,
[IsOtherInformationVerified] [bit] NULL,
CONSTRAINT [PK_NA27] PRIMARY KEY CLUSTERED
(
[RecordId] ASC,
[BatchId] ASC,
[ImageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
**Database Properties**
Collation: SQL_Latin1_General_CI_AS
Recovery Model: Full
Compatibility Level: SQL Server (100)
Please suggest.
we are not able to retrieve deleted record for given script
this gives compatibility issue but we are using sql server 2008 (100)
procedure executed successfully but when we access deleted record through executing procedure create error
please give your valuable response.
Hi Prashant, what is the error message ?
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
and im using sql server 2008 (100)
Plz comment line no 18 to 20 in the sp to avoid this error
this is table structure when i delete some row from this table rows are not retrived
Id bigint
ProjectId bigint
TaskId bigint
UserId bigint
Notes nvarchar no 6000 SQL_Latin1_General_CP1_CI_AS
RecordTime datetime
IsDoc int
isWorkLog int
RemarkId bigint
gives error as :
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
Hi Muhammad
I get the same error as “prashant”.
Please any idea.?
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
Thank you
Zouhaier
Hi,
It means that you do not have data in you log. Please check your recovery model, it should be full.
Hi,
Thank you for your reply
I start my test with a blank database.
in my database I have a single table that matches your example.
Parameterization recovery is “FULL”
when I run the stored procedure the following error appears:
a problem of convertion datetime to nvarchar
(8 ligne(s) affectée(s))
Msg 242, Niveau 16, État 3, Procédure Recover_Deleted_Data_Proc, Ligne 68
La conversion d’un type de données nvarchar en type de données datetime a créé une valeur hors limites.
L’instruction a été arrêtée.
Msg 50000, Niveau 16, État 1, Procédure Recover_Deleted_Data_Proc, Ligne 180
There is no data in the log as per the search criteria
Thank you
Most thanks to you… it works greatly… I recover my data for my client, Really it is vary helpful for me…
Excelent
Hi Muhammad, many thanks. This is great. It’s worked. You saved my day. Jazakallah.
Thanks a million!!! May Allah bless you for this service to mankind
It doesn’t work if the datatype is time, can you help me out on this?
[…] How to recover deleted data from SQL … – In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very easy to …… […]
[…] How to recover deleted data from SQL … – In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very easy to …… […]
Reblogged this on Keep Software Testing Weird and commented:
Recover DELETED DATA FROM SQL Server
[…] How to recover deleted data from SQL Server | SQL Server … – 2011-10-22 · In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very …… […]
[…] How to recover deleted data from SQL Server | SQL Server … – 2011-10-22 · In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very …… […]
my machine has restarted and I am unable to retrieve the deleted records. Please provide any suggestion.
[…] How to recover deleted data from SQL Server | SQL Server … – 2011-10-22 · In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very …… […]
Hi Imran,
I am not so good in sql database…..in my site i have Server2008 r2 and sql 2008 database .Our client is using sql report service 2008 for their daily and monthly reports… total 8 nos of reports they are generating ….Now among this report one report has serious issue…while generating report for a period one month it goes on loading for long period and we will not get the report finally….during this period of report generation …..the database server goes on high level of CPU memory consumption and whole database operation get hanged up (basically data entry to the database)……
Recently while on goggling i got some queries ,ie,
USE AdventureWorks2012;
GO
EXEC sp_updatestats;
after executing this query i restarted all process of sql….so after this i could generate that one month report for short period of time without any hang….the same problem will restore again after some time…..please help me…. im looking for a perment solution for this….
Dear Imran,
can this deleted rows recovery script work with sql express? Below are my server details.
Collation: Latin1_General_CI_AS
Microsoft SQL Server Express Edition (64-bit)
Microsoft Windows NT 6.2 (9200)
Platform: NT x64
Version: 10.0.5520.0
It hangs. I created your table and ran sp but it hangs.
Dear Imran,
kindly note that my service pack is sp3
Dear Imran,
I created Martin Smith’s #hex table and executed:
“SELECT LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
FROM #hex”
and it hangs. In case this is a clue. Your help is much appreciated.
Dear Imran,
apologies. The tool has worked for me. I just needed more patience. It took about 14.23min to retrieve 16 deleted rows. See my exec command below:
EXEC Recover_Deleted_Data_Proc ‘erequestubn_test’,’ereq.atm_bulk_stage_1′,’2014-12-14′,’2014-12-16′
I just wonder how long it might take to retrieve about 250,000 deleted rows. However, the 250,000 rows is on a more robust server machine.
Imran, you are the man.
Kind regards
Uzy
U are Great Boss. Its really more helpfull for me.
Thank You
Hi Imran,the sql procedure script have not downloaded,please provide me a copy, thanks. my email is prcgolf@sina.com.
Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
DB compatibility level is SQL Server 2008 (100)
SQL Server recovery model if FULL
I desperately need this solution working however it fails at myend.
For starters, the example you gave above, i tried running that locally and i get the below error:
Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 511
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 637
XML parsing: line 1, character 2, illegal xml character
Any idea?
Please send me your SQL Server details
Fantastic piece of work Muhammad. Keep up the good work.
[…] blog post covers in more detail finding the time to STOPAT using fn_dblog. This blog post has a go at recovering data directly from the log but this might be very time consuming […]
Thanks. Great program !!!
Muhammad Imran
Hi
I had tested like below sql. but not appare any data.
Could you help me?
” Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName ='[sspp].[dbo].[Delivery_Note]’ AND Context IN (‘LCX_MARK_AS_GHOST’,’LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’) “
Hi Muhammad , i run with this error
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria.
Evene i have log recor
HI Muhammad , even I test with test table , it can’t recover delete record from sqlserver standard edition 2012 , with below error
Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 637
XML parsing: line 1, character 2, illegal xml character
,
Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 511
Invalid length parameter passed to the LEFT or SUBSTRING function.
Hi
Thank you for this post. Is there any chance to recover data while the backup option of the database was on set on simple?
Thanks
[…] 以下为译文：https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ […]
[…] https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ […]
Hi Mohammad,
Your research is great.
Yesterday I deleted data from one of the table and then I executed your stored procedure. I could see the data in it. But that data was not found in the table.
After that server got shut down and now I can not see any data into it.
Any idea how can I recover it?
Hi Mohammad,
Your site is helpful.
Yesterday data from one of the table got deleted. And I could see the records using your stored procedure. Thanks to you. But today I am not able to see the records using same stored procedure.
Any idea how can I recover it now?
Hi Sam,
Unfortunately, you cannot recover your data after restart.
Imran
m trying to recover 5 lacks rows of table which has 7 columns.
when I run the SP it is running continiously. for me it ran for 4hrs then I stopped it forefully. What should I do?
Plz send me table structure along with sql server details
Hi Imran,
The database is set up as full recovery mode
accidently all records (5 to 6 lacks rows) got deleted of this table.
Table structure as below –
CREATE TABLE [dbo].[tbl_SETKM_User_Activity_Log](
[Asso_Id] [varchar](20) NOT NULL,
[URL] [varchar](256) NOT NULL,
[Time_Stamp] [datetime] NOT NULL,
[Response_Time] [int] NULL,
[Source] [varchar](256) NULL,
[Asso_Name] [varchar](256) NULL,
[Band] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Department] [varchar](256) NULL
)
below are the sql servr details –
Microsoft SQL Server 2010
Microsoft SQL Server Management Studio 11.0.3000.0
Microsoft Analysis Services Client Tools 11.0.3000.0
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.9600.17801
Microsoft .NET Framework 4.0.30319.34014
Operating System 6.3.9600
Thanks for taking time to resolve this issue.
Hi Imran,
The database is set up as full recovery mode
accidently all records (5 to 6 lacks rows) got deleted of this table.
Table structure as below –
CREATE TABLE [dbo].[tbl_SETKM_User_Activity_Log](
[Asso_Id] [varchar](20) NOT NULL,
[URL] [varchar](256) NOT NULL,
[Time_Stamp] [datetime] NOT NULL,
[Response_Time] [int] NULL,
[Source] [varchar](256) NULL,
[Asso_Name] [varchar](256) NULL,
[Band] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Department] [varchar](256) NULL
)
below are the sql servr details –
Microsoft SQL Server 2012
Microsoft SQL Server Management Studio 11.0.3000.0
Microsoft Analysis Services Client Tools 11.0.3000.0
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.9600.17801
Microsoft .NET Framework 4.0.30319.34014
Operating System 6.3.9600
Thanks for taking time to resolve this issue.
Now I got below error inbetween..
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 191
There is no data in the log as per the search criteria
What does that mean?
I took backup of log file and after that this error came up, is this bcoz of taking backup of .ldf file?
Hi, thank you very much. It’s amazing
but, how can I add the userName how delete the records to the results
thanks again.
hii sir, i execute this procedure and it displaying executing for long time, how much time it take to execute ? plz reply..
Hii Sir, i exceute this procedure and its displaying executing since long time such as half and hour and not displaying any result, why this happening ? please reply sie
Hi Sandip,
It depends upon the number of records you want to recover.
Imran
I tried to recover deleted records but error occured following.
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 190
There is no data in the log as per the search criteria
D J. KADAM
Realy Thanks Thanks a lot . It saves my life. . Really Thanks
Thanks a ton for the SP. Its too helpful.
[…] the logs and convert them from the default hexadecimal to a readable format. There is a great article by Muhammad Imran on this […]
great aricle.. great help for me.. thanx alot…..
[…] How to recover deleted data from SQL Server | SQL Server … – In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?” Now, it is very easy to … […]
Sa. Muhammed
Is it possible if recover model=simple mode ?
and
After accidentally deleting the data with delete,
“select * from fn_dblog (null,null)” was a lot of information with this command.
Then I got a database backup.
Again when I run “select * from fn_dblog (null,null)” command, the previous information did not come.
Great Man, Your query will save lot of time and my client as well 🙂
Really it’s super..
Dear Imran
I am proud of you – superb work.
Is there any possibility that I can use this procedure in SQL SERVER 2000.
Thanks
S.Imran
Thanks Imran. You can try given below link for SQL Server 2000. https://raresql.com/2012/01/23/how-to-recover-deleted-data-from-sql-server-2000/
HEY MAN THANKS A LOT U JUST SAVED MY LIFE 🙂 …. GREAT WORK
Thank you, bro. God will help you in the same way that you helped
us
Thank you !!! You are a life savior!
I just added a new parameter to your script
,@NewTableName NVARCHAR(100) = NULL
and the last sql execute script:
SET @sql = ‘SELECT ‘ + @FieldName +ISNULL(‘ INTO ‘+@NewTableName,”)+ ‘ FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘)) AS pvt’
So to be able to insert into a defined new table the data deleted and manage it in any way I want later on.
hi, Muhammad Imran
thank you for this, it’s really good
but i need to know
after recover deleted data, the log become empty?
i asked if there is an solution for recover data without deleting log?\
thanks in advance
Hi!
please help me!
I’ve run it show error:
“Msg 210, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 1274
Conversion failed when converting datetime from binary/varbinary string.”
my table struction is:
CREATE TABLE [dbo].[BUSINESS_SEARCH](
[BS_No] [int] IDENTITY(1,1) NOT NULL,
[BS_BC_No] [int] NULL,
[BS_Kind] [char](1) NULL,
[BS_AccountID] [varchar](50) NULL,
[BS_Id] [varchar](100) NULL,
[BS_Name] [varchar](50) NULL,
[BS_Name_idx] [int] NULL,
[BS_Telno] [varchar](20) NULL,
[BS_Email] [varchar](200) NULL,
[BS_Status] [char](1) NOT NULL CONSTRAINT [DF__BUSINESS___BS_St__1FB8AE52] DEFAULT (‘1’),
[BS_MI_Code] [varchar](10) NOT NULL,
[BS_Regdate] [datetime] NOT NULL CONSTRAINT [DF__BUSINESS___BS_Re__20ACD28B] DEFAULT (getdate()),
[BS_U_MI_Code] [varchar](10) NULL,
[BS_U_Regdate] [datetime] NULL,
CONSTRAINT [PK_BUSINESS_SEARCH] PRIMARY KEY CLUSTERED
(
[BS_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
my table example data is :
Insert into [dbo].[BUSINESS_SEARCH] (BS_No ,BS_BC_No,BS_Kind,BS_AccountID,BS_Id,BS_Name,BS_Name_idx,BS_Telno,BS_Email,BS_Status,BS_MI_Code,BS_Regdate,BS_U_MI_Code,BS_U_Regdate)
values
(‘1032′,’1955′,’NULL’,’4′,’Jay’,’063-123-1234′,’cloud@gmail.com’, ‘1’,’1′,’200806-002′,’2013-02-19 15:51:00.403′,’200806-002′,’ 2013-02-19 15:51:00.403′)
sorry, my table example data is wrong.
my table example data is :
Insert into [dbo].[BUSINESS_SEARCH] (BS_No ,BS_BC_No,BS_Kind,BS_AccountID,BS_Id,BS_Name,BS_Name_idx,BS_Telno,BS_Email,BS_Status,BS_MI_Code,BS_Regdate,BS_U_MI_Code,BS_U_Regdate)
values
(‘2668′,’961′,’4′,’google’,’goryeo’,’ber’,’10’,’1′,’201207-001′,’2013-02-04 14:40:12.930′, ‘201207-001’, ‘2013-02-04 14:40:12.930’
)
Excellent work >>>
Thank you Muhammad Imran
Excellent work dear Muhammad,
but when i examined this procedure at my real database, i noticed that the procedure is giving errors when the table structure have been changed since last delete (eg. dropped columns, new added columns).
solution easy if we ignored dropped columns, by adding this line of code :
“And syscolumns.NAME is not null ”
when inserting data to @ColumnNameAndData
best regards
Hello
really good job,
thanks a lot,
worked perfect for me with adding “is_dropped = 0”
because of error name is null.
This works great, but I have a problem. A transaction log backup ran just after the delete was done, so there is nothing in the current transaction log. Is there any way to pull from a previous transaction log?
You will need to look for fn_dump_dblog and call the log backup file that was created right after the delete command.
Hi Bob, I had this same question, and it turns out you can query transaction log backups with the fn_dump_dblog function which has the same output schema as fn_dblog – I’ve made a change that allows you to specify the log file which I’m currently testing, and early results are promising.
I just want to appreciate that you wrote this stored procedure. this is just too amazing. It worked on my machine. one thing is that I had to install the stored proc on the database I need to run on. else it can’t find the table. so I was wondering if I’m doing something wrong or database_name parameter is just there 🙂
[…] How to recover deleted data from SQL Server […]
Excellent Article.
It saved my time
[…] https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ […]
[…] The first method I have already discussed in my previous article “How to recover deleted data from SQL Server”. […]