[Last Call] Learn how to a build a cloud-first strategyRegister Now


database and data retention policies

Posted on 2014-08-05
Medium Priority
Last Modified: 2014-08-21
I am trying to understand further data retention policies and how at a technical level they are implemented. I appreciate certain compliance regulated data may need to be retained and archived in line with legal requirements and/or compliance legislation. Likewise PII data can only be kept for the purposes it was collected in line with privacy legislation and no longer before it must be erased.

But I don’t quite understand technically how this is done. Say for example a database with personal records – if you never delete the records out of the online database does that constitute an effective retention policy, or if not why not?

I am sure the backups and tape backups come into this somehow but if the online copy of the DB contains all the records in the database going back years, then why do you also need to keep endless backup copies and tape copies as part of the retention policy? It would make sense if you are taking a snapshot of data each day and then records are deleted from the online copy of the DB, then it would make sense if you ever had to restore old historic data – but if all data ever collected sits in the same DB then I am a bit lost how the whole retention thing works and how it ties in with backups and archiving..
Question by:pma111
  • 2
LVL 23

Accepted Solution

nemws1 earned 1000 total points
ID: 40242111
Sometimes somebody deletes/changes something and nobody notices for a few months/years (ever write an UPDATE statement and forget the WHERE clause?).  Especially if the data is archived data and nobody is actively using it for analysis.  Such a retention policy is purely CYA.  Also, some auditors don't care if you claim to never delete data, they want a copy of the data as it was on the date in question (or as close to it as possible).  Probably doesn't make sense, but it keeps things legal.
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 1000 total points
ID: 40242179
I am sure the backups and tape backups come into this somehow but if the online copy of the DB contains all the records in the database going back years, then why do you also need to keep endless backup copies and tape copies as part of the retention policy?
Because data changes. Names change, balances change, addresses change, phone numbers change, product names and brands change, prices change, etc. etc.

Each retained backup provides a snapshot of the full dataset at the time the backup was made.  The data online only provides the current state of the data.  Knowing what it said at point X in history is important in most data retention policies.

This also provides protection against having your system cracked. If, suddenly, your disk array is encrypted by malware, your backup is your immediate recovery. It provides protection when (not if) your disk subsystem fails, or a computer failure corrupts data within your database.

There are more reasons - these are just some of the most important.

Author Comment

ID: 40243303

Do either of you work with any applications/databases whereby you can only keep data for so long before it has to be scrapped, and any technical challenges in that area? This is common with personal data which should be removed when no longer required for the purposes it was collected.
LVL 23

Expert Comment

ID: 40243764
Yes, we deal with credit card information (no, we do *not* store the whole credit card number).  When a customer leaves us, we need to delete their data.  That particular chain of data we are very picky about.  When a customer gives us their credit card number over the phone, our voice recording system stops recording (happens automatically when our agent goes to the credit card section of the customer data screen on our internal software).  The data is encrypted client-side before it is transferred anywhere.  We run a daily process to go through and remove any CCs that have expired (although we do *not* delete the customer record for that CC - we just blank out the info and attach a note to the record that CC expired).

You are correct, though, in that we *do* keep our backups for extended periods of time.  Those backups are to physical media and placed in a safe.  Any data restores we've done from those have been to non-production hosts that are unavailable to the outside network (to my knowledge, at my current company, this is a very rare thing to do).

I worked for 10+ years at a university, which *did* collect and store everybody's SSN, in plaintext in a table for years.  Yes, they've fixed that and made other changes, but they have years of backup tapes sitting on shelves in their server room with tons of data on it that is private.  Yes, their operations room is controlled, but somebody could take a tape or two and nobody would ever know.

The best thing to do is identify any sensitive data that you know will need to be purged and make sure you have some control structures on your table to handle that (like modified-on and modified-by fields along with a note  field or better yet a change log table).  My policy is never to delete a row no matter what.  I mark them as inactive and blank out data from a table, but I pretty much never do any deletes.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
"Any files you do not have backed up in at least two [other] places are files you do not care about."
Viewers will learn how the fundamental information of how to create a table.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question