Roll back a transaction

Question

Hi Friends,

I am updating some data in a table using transaction. The log of the transactions are available in the ldf files of the database. Now by mistake a user has committed a transaction of deleting records and it got committed and the data got deleted. so is there any way of rolling back that particular transaction. Please, suggest. I would really appreciate your help.

Thanks,

Answer 1

You cannot rollback a committed transaction.

You're going to have a 'point in time recovery' of the database prior to the transaction commit. You start from the last full backup prior to the incident, then apply the latest differential prior to the incident and then all the logs up the point of interest, and stop at the time of the incident, when you recover the database w/o applying any further log. More details in the link. If you have other transactions after that point in time that need to be recovered, then you can restore a copy of the database at the point of the error and then manually copy the deleted data from the copy database into the real database.