How do you recover from a user deleting data in you database?

Question

Physical disaster such as hardware failure (duplicate server)
User accidentally deleting data (dunno)
Malware or hacker deleting data(snapshot & duplicate server)
Software malfunction such as a corrupt database(snapshot)

I am sure there are other restore methods. Can't think of them.
How do you recover from a user deleting data in you database?

Answer 1

Ideally you will have set security on the database so that the user can't delete data in the first place.



However, the only way of getting that data back is through restoring a backup.

Answer 2

Right, This could easily turn in to a essay with discussions on hardware to prevent issues, software design, server mirroring, disk mirroring, but rather than doing that i will try and stick to the user deleting data, there are 2 types of users here. we have users that access the system via some application, and system administrators who willhave direct access to the database.



For the first user type (those accessing via an application)

If they are to have access to delete items having a combenation of IsDeleted Flags and/or audit tables (which could hold every change ever made) could allow you to implement a system where undo deleted would be available to the user incase of mistake.



For the second user, with most sql database system you can lock down users abilitys so they would not have the right to delete , or view ceritan data from a security point of view this should be implenented, as well as it would stop adminsitrator having a go aat fixing a system that they do not understand. But in the end back ups are what you need.



With most Database management systems there are several types of back ups fbut for simplisity Lets say there are full back ups and log back ups. where with a log back up you use the last full back up to reach the point where the log back up was taken.



The level of back ups will depend on how much lost work your company can afford, say they can handle the lose of a days work then you would perform a full back up every day with a log back up at midday. if they coudl only handle a hours then you would have hourly log back ups with a full back up at night when the system is used less.



if they needed more than that the you would mirror the database with about 15 minute delay, so you can stop any admin mistakes and switch to other database.



BAskically a back up model comes down to:



How much data/work can the comapny afford to lose? (a da, a hoour, a minute)

How long can the company wait till the system is restored? (mirror required, back up regimie,)

How much can the company afford hardware software wise? (raid arrays, cluster servers, cloud computing, secondary servers, ups, etc)



Please remember all databases should be backed up to a seperate drive and perferibly stored off site.



Final note if hackers or far worse malware can get to your database then there is something seriously wrong with yoru security model, though the recovery model with be the same.



Anyway this is a rough recovery model.



Good luck

Answer 3

backup. That is the official method of protecting against failure. However, with the user thing - he shouldn't have access to the database. Restrict his movement. I usually just give them access through the interface (there's a whole new set of passwords protecting the database). Even if he figures out how to reach the database, he will have to break the passwords there. also, make sure you're protected against sql injection attacks (which most frequently occur due to some programming error)

Answer 4

File Backups.

=

Answer 5

there's no excuse, you should have a cron job to make daily backups and you should be downloading the backups in the middle of every week.



im assuming you are asking the question out of a school assignment, well, that would be my answer.