How to recover deleted/updated record in sql server or mysql?

Question

I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?

Answer 1

It depends on how you updated it and what backup/disaster recovery strategy you use.

If you are performing the DELETE within a TRANSACTION, the action can always be rolled-back until you COMMIT it.

Perhaps you're logging/auditing such operations using a trigger or similar, in which case you could find the data that was changed and do the 'opposite' to restore it.

Perhaps you're replicating the database every so often to another server - you could restore the data from there.

Perhaps you're performing backups to disk or tape in which case you might have to perform a full restore to get it back.

Failing all that - and short of forensic recovery - unless you can remember the data that was there, it's gone.

Answer 2

You always have a choice to use a recovery tools, such as ApexSQL Log for example. Not only that you can recover deleted rows, but you can also choose which rows you are going to recover.

It can recover your data from on-line, backup or detached transaction logs.

I have tried that tool and it could be the life saver.

See: How to recover SQL Server data from accidental UPDATE and DELETE operations

Answer 3

Once the row is deleted it is gone. You can will have to use a backup to restore the data.

The exceptions to this are if you are doing a delete inside an open Transaction, in those cases you can "Rollback" the transaction to undo any changes made inside the transaction.

Answer 4

Don't really think so. Once deleted, it gets deleted permanently... This is a bit old, but haven't heard of anything else: http://forums.mysql.com/read.php?21,135990,137776#msg-137776

Answer 5

Checkout this thread would be help you :

http://technet.microsoft.com/en-us/library/dd207003.aspx

If the above url did not help you, you can try to recover this third party RecoveryFix for SQL Software. This software will restore data in working condition.

Thanks

Answer 6

This can is probably the most heartbreak/gut-wrenching subject any database professional has to deal. Naturally, one should have backups and perhaps some binary logs to replay up to second before that fateful moment you lost that table (or even that whole database).

Percona has the good sense to create a tool to delay a MySQL Replication Slave

• First, it was MAATKIT's mk-slave-delay.
• More recently, Percona came out with pt-slave-delay.

The general idea was to create a Slave that was deliberately set back in terms a certain interval. For example, suppose you had a Slave that was delayed one hour. If you accidently deleted a table on a Master, you have a golden opportunity : A one hour window to do the following:

• Copy the Slave's copy of the deleted table back to Master
• Use the Master's Binary Logs to locate
• If the recovery will take longer than the window of time, just run STOP SLAVE and continue

Of course, the drawback would be to have another Slave (additional hardware and copy of the data) for that purpose. Then, again, that's why people buy insurance policies.

Oracle now has implemented this, not as a separate tool, but as part of MySQL 5.6.

The CHANGE MASTER TO command for MySQL 5.6 now has the option called MASTER_DELAY. You would set up the Slave with this command for a one-hour delay:

CHANGE MASTER TO MASTER_DELAY = 3600;

This will delay SQL commands and transactions by this interval.

Please see this explained in this YouTube Video