database and data retention policies

Posted on 2014-08-05
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
    LVL 23

    Accepted Solution

    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

    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.
    LVL 3

    Author Comment


    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    The viewer will learn how to start File History, a MACINTOSH like backup utility built into windows 8, on your Windows 8 computer. To open the File History control panel swipe from the right  side to get the search menu or position the cursor in the…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now