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: 63503 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101095 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grant Fritchey SSC Guru Points: 398882 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: 55548 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: 101095 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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: 101095 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".