database and data retention policies

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..
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brendt HessSenior DBACommented:
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.
pma111Author Commented:

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.
nemws1Database AdministratorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.