How to keep and restore the deleted records?

Question

How to keep and restore the deleted records?

Making Future

Mr or Mrs. 500

Points: 517
More actions
May 19, 2003 at 11:18 am

#81911

Hi all,
My SQL2K has over 100 tables and some triggers for tracking who deletes records. I intend to keep the deleted records in somewhere, so that if the records are deleted by accident, I can restore those records.
Is there any way to do it?
Thank you!

Viewing 15 posts - 1 through 15 (of 16 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Best way to do it is create "Pending deletes" tables. Create a copy of your tables by generating a script and add a insert option to your triggers which inserts the delete values (maybe together with the date of deletion) into these tables. I would also create a job which cleans these tables evry once in a while.

[font="Verdana"]Markus Bohse[/font]

Making Future Mr or Mrs. 500 Points: 517 More actions · Answer 2

Good idea.

One more detail, do you mean insert a copy of whole table into the Pending Deletes?

Would you advise me how to restore the table

if I choose this way?

Thx

DRBuckingham SSC Eights! Points: 996 More actions · Answer 3

Insert only the "deleted" record into the "pending deletes" table. You can then periodically bcp out the table, back up/archive the bcp file, and finally truncate the "pending deletes" table.

If you need to restore the records, you can bulk insert (or bcp in) the records from the archived bcp file into a temp table, then INSERT INTO <YourTable> SELECT FROM #tmpRestoreTable WHERE <YourFilterCriteria> to "restore" them. You may need to specify a field list if you've added any additional fields to your "pending deletes" table.

If the records still exist in the "pending deletes" table, you can issue basically the same command, but substitute the "pending deletes" table name in place of "#tmpRestoreTable" in the above INSERT INTO command.

David R Buckingham, MCDBA,MCSA,MCP

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi,

sorry for asking this, but how can your records be deleted by accident

I use another approach to 'delete' records. I don't allow a physical delete, but a logical delete. Each production table contains a bit field 'deleted', which is set to TRUE when a delete action is completed. These records cannot be viewed within the GUI, but they are still in the db, so I can yery easily restore them if needed.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Making Future Mr or Mrs. 500 Points: 517 More actions · Answer 5

Hello,

Your ideas are great and I’m going to try bcp.

For a5xo3z1, your question is good. Actually, our data is used to statistical research. So some dirty data must be deleted periodically. Sometimes data managers who have delete permission make mistakes. Well, I’ll think about your way seriously.

Thank you for your help.

carlosgb Newbie Points: 5 More actions · Answer 6

A tool like Log Explorer from Lumigent is useful to roll back unwanted transactions such as deletes by accident.

powermad SSC Rookie Points: 48 More actions · Answer 7

quote:
sorry for asking this, but how can your records be deleted by accident

BOFH

Seriously though, you always find users who contact you and say "Oh, I deleted the main list of companies, can you retrieve it for me?" You ask why they deleted it and the reply is often "I wanted to see what it would do"...

I even had a 3 step "Are you sure?" thing which required validation on each, and 5 minutes later got a call "Oh, I've deleted this now, can you get it back"

Even if you make something idiot proof, someone will come along with a better idiot!

Crispin Proctor SSCoach Points: 18161 More actions · Answer 8

I've had people walking up to my desk and ask (You can tell what they about to ask!) "Did the backups go through last night?

Same story, "Are you sure you want to delete?" and they say yes.

I once added extra confirmation "Ask the person next to you if you want to delete this item!" > "Did he/she say yes?"

I actually forgot about it and it got shipped with these prompts in.....

We did a MS and released a Critical Update the second it was discovered....

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hi powermad,

quote:
Seriously though, you always find users who contact you and say "Oh, I deleted the main list of companies, can you retrieve it for me?" You ask why they deleted it and the reply is often "I wanted to see what it would do"...
I even had a 3 step "Are you sure?" thing which required validation on each, and 5 minutes later got a call "Oh, I've deleted this now, can you get it back"
Even if you make something idiot proof, someone will come along with a better idiot!

I know what you mean!!!

It is impossible to make anything foolproof because fools are so ingenious.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 10

I prefer to use a deleted date field that acts as a flag in the critical tables.

This means that I can also identify when a record was deleted. When these "deleted" records exceed a certain age then you can archive them off to a copy of your live database.

If you are scared of your users deleting things en-masse then put some sort of catchall in a DELETE trigger that only allows single record deletes.

If you have data referential integrity set up then they cannot delete stuff from tables on which a foreign key depends.

LinkedIn Profile

AFPeterson SSCommitted Points: 1580 More actions · Answer 11

Some good stuff here.

Sounds like you already use triggers to capture who deletes, so, as mentioned above may want to add to the trigger to capture the deleted data.

You can put a copy into a deleted table, a history table (for all changes, not just deletes). Or, use the date field (above) or a deleted flag field.

Each approach has pros/cons, so understand the business goals, user loads, concurrency issues, etc. to determine the best technical solution.

What's the business problem you're trying to solve?

PBirch SSCrazy Eights Points: 8493 More actions · Answer 12

I am a big fan of the "deleted" flag. Not a big fan of anything that actually deletes the record.

I also will use the archive database - mark the records and move them into another database based on date. That way if they mark the wrong records it's not that much of a problem to move them back.

I use DTS instead of bcp - only because bcp requires us to be on the server and that box is not anywhere near me and I don't want to vpn everytime I run the procedure.

Merci,

Patrick

Quand on parle du loup, on en voit la queue

Louis_See1 Ten Centuries Points: 1100 More actions · Answer 13

I am very interested on this topic.

Anyone can share with me the trigger syntax to delete and insert into "pending deletes" table?

ljmonteiro SSC Enthusiast Points: 126 More actions · Answer 14

a5xo3z1 use a "status" field ( I add also a timestamp , and a user field ). But adding a column on a production table sometimes it's not possible, but useful on critical DBs.It's also good check if this additional processing will have "side effects" in operation .