Hi,
by mistake I fired an update query and all the records in the table in dat field got updated and also cannot rollback as auto_commit is on.
Is there any other way to retrieve the records?
Please help, its urgent. Thanks in advance.
views:
34answers:
3
A:
You can get the records but they are in the state of the last update. Rollback does not work, cause the transaction is closed already. (auto_commit on).
khmarbaise
2010-04-27 10:33:43
+1
A:
I don't think you can rollback a query/transaction once it's been commited (and, here, it has) : the old data has been replaced by the new one, and has disappeared.
As possible solutions :
- If possible, depending on the update you did, there might be a way to revert that update ?
- Else, I hope you have frequent backups, and know how to restore them (to a temporary database, if necessary, to extract the informations you need, and re-import it into your production database "by had")...
Pascal MARTIN
2010-04-27 10:34:26
yes i have backup but d recent data will be lost.:(
devang
2010-04-27 10:35:53
If you restore it to your production database, yes ;; but a solution could be to restore that backup to another database ; and, then, extract only the data your need from that temporary restored database, to import those manually to your production database *(I didn't say it would be easy...)*
Pascal MARTIN
2010-04-27 10:37:21
ya i know will try figuring out if your solution works..thanks
devang
2010-04-27 10:59:32
You're welcome ; and good luck...
Pascal MARTIN
2010-04-27 11:00:48
A:
If you've got binary logging enabled, you could restore the database from backups, then extract whatever changes were between the backuptime and your bad query from the binary log and re-apply them to the database.
Marc B
2010-04-27 15:19:14