After using delete statement in sql query to delete some records...to retrieve

After using delete statement in sql query to delete some
records...to retrieve the deleted records we can get using
rollback command but till that where it stores means
particular location name i need....(after deleting and
rollback )

Question Posted / priyanka

3 Answers
11620 Views
CarrizalSoft Technologies, iGate, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

After using delete statement in sql query to delete some records...to retrieve the deleted records..

Answer / nagesh

wen we are performing DML operations into table . 2 magic
tables created in the memory i.e., in Master Database.
Names of the 2 tables
1) INSERTED
2) DELETED

When we insert any record then that record will be added
into this Inserted table initially, similarly while
updating a record a new entry will be inserted into
Inserted table & old value will be inserted into Deleted
table.

In the case of deletion of a record then it will insert
that record in the Deleted table

when we call rollback command the deleted record retrieved
from Deleted table. Once committed we can't call rollback.

Is This Answer Correct ?

18 Yes

5 No

After using delete statement in sql query to delete some records...to retrieve the deleted records..

Answer / saravanan p

When we delete the records using triggers only the deleted
records will be stored in deleted table.

But if we do delete using begin transaction, the deleted
records will be stored in transaction log file. We cannot
see it visually.

Is This Answer Correct ?

5 Yes

1 No

After using delete statement in sql query to delete some records...to retrieve the deleted records..

Answer / imran

SQL Server keeps log for each deleted records. you can
query these logs via 'fn_dblog' sql server function.

Select [RowLog Contents 0] FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.TableName' AND Context IN
( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' ) AND Operation in
( 'LOP_DELETE_ROWS' )

But this log is in Hex format. and you need to convert this
Hex format to your actual data.

Given below is the article that can help you to recover the
deleted records in the same way defined above.

http://raresql.com/2011/10/22/how-to-recover-deleted-data-
from-sql-sever/

Is This Answer Correct ?

3 Yes

1 No

Post New Answer