Roll back a transaction

Question

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.

Answer 2

Reading a transaction log using the built in SQL Server functions and commands, such as fn_dblog and DBCC LOGINFO can be difficult because all data is stored in a hexadecimal format. Manual querying and converting is necessary in order to get the readable data. These are undocumented functions and can't give out of the box results. Theoretically you could track down deleted record and see what has been deleted

Please note that recovery model is also very important when it comes to recovery of data, FULL or BULK LOGGED model should be used

Regarding the point in time recovery you can take a look at article which describes procedure using SQL Server Management Studio and third party tool called ApexSQL Log. There is other third party tool like RedGate's SQL Rescue also. Please note that you have to provide full chain of transaction log backups: the chain of transaction log backups since a full database backup exists, or a full database backup, then differential database backups and then the transaction log backup chain from there up to the point in time you want data recovery.

Disclaimer: I work for ApexSQL as support engineer