recover table data which is accidently updated all the row in employee table

Question

Post reply

recover table data which is accidently updated all the row in employee table

wasim.ahmad780

SSC Veteran

Points: 289
More actions
December 27, 2020 at 7:52 am

#3824410

Hi,
I have mistakenly updated all values in employee table how to recover that

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

If you're using full recovery mode, restore up to just before you ran that query?

ScottPletcher SSC Guru Points: 100947 More actions · Answer 2

First Possibility:

Restore the last backup of the db to a different db name, then copy the table employee table from the restored db over the employee table in the original db.

Of course that may depend on how many changes have been made to the table since that backup.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

You may have to restore to a new database and then migrate the data over manually or using a tool.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55539 More actions · Answer 4

Was it updated with a manual UPDATE EMPLOYEES SET FIELD_A = 'ABCDE' ?

If so, a suggestion for future manual updates, always put manual updates in a transaction, so you can rollback if needed.

Just be sure to run COMMIT once you are sure the update was correct.

Were you able to recover ?

ScottPletcher SSC Guru Points: 100947 More actions · Answer 5

It's more involved, but you can also create a trigger(s) that insures that a WHERE clause was included on all DELETEs and UPDATEs. And add an override just in case you really do want to DELETE or UPDATE all rows.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Won't help if the WHERE clause just happens to include the entire table. Seen it, had to help recover 10 years of data as a result of an incorrect date range entered in a query.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 7

Well, yeah, it won't cover everything, but then again nothing will. It prevents the most common thing where someone just leaves off the WHERE clause.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.