question

ravikvs avatar image
ravikvs asked

how to see the deleted rows in again in with SYS.Fn_dblog(null, nulll)

Here is the problem I have accidentally deleted all rows from a table. After that i have run the following command select * from SYS.Fn_dblog(null,null) where operation('deleted-rows') and allocUnitName = 'mytablename' I am able to see all the deleted rows. But that data is in hexa decimal format. I have found some stored procedure from the net to convert hexa decimal data to actual data. Before run that stored procedure, for safe side i have taken the full backup of my database. Once backup is over. Just I ran the following select statement again. select * from SYS.Fn_dblog(null,null) where operation('deleted-rows') and allocUnitName = 'mytablename' But all the deleted rows information is gone. I am unable to see the delete rows what i have seen previously. Please guide me that what is the route cause for this? with regards, Ravi KVS
record
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
If your database is in simple recovery mode, you just caused a checkpoint to be issued and the log information has been removed. That data is now gone. You're stuck. If your database is in full recovery, you would have to have run a log backup to have that information removed from the log. If you have that available, I'd suggest restoring your database to a point in time just before you ran the DELETE statement to a second location. You can then retrieve the data you removed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.