When to Archive Data
Databases can grow exponentially. No matter what you track, your database probably grows year after year. If you are using your database to keep track of customers and sales, additional transactions likely accumulate every day. If you are using your database to track students or people, your database will grow each time somebody registers or enrolls. Even if you are just using your database to track your household items or collectibles, it will continue to grow as you accumulate more items. No matter how powerful of a computer system you are using, eventually you will find that performance degrades as your data storage requirements grow. Fortunately, there is a solution – archiving data that you no longer need.
What is Database Archiving?
Database Archiving is simply the process of removing data from your database that you do no longer need, and saving it to long term storage media. By saving the archived data you can still access it if you need to, but it will no longer impact your system performance for day-to-day tasks.
Please note there is an important distinction between backing up your data, and archiving your data. When you backup your data, the original database file remains the same. When you archive your data, the archived data is removed from the original database.
After you archive data, you will notice many benefits. Your core database performance will improve as your database needs to track less day. Backing up and storing remaining data becomes much faster as there is less data to track.
When Should You Archive Data?
Data should be archived when you think you will no longer need to access it. Examples may include sales data from previous years in a point-of-sale database; past student enrollments in a class list database; products that you no longer have in a collectibles database; employees that have left your company in a human resource database; and broken equipment in an equipment database. Basically, any time your database is tracking an item or record you no longer need to track, it’s a good candidate for archiving.
But just because you are archiving data it doesn’t mean you can never access it again. As long as you save a copy of your archived data, you can refer to it in the future if the need arises for reporting or audit purposes.
Data Archive Strategies and Data Archiving Preparations
Before you archive data, you may want to generate some reports that provide you with a snapshot of the data you are going to archive. For example, while you may not need to access individual sales from years ago, you still might be interested in a statistical summary of sales for past years. Having this type of information is obviously handy as it lets you gauge your current performance compared to previous years. However, if you were to archive this data, it could be cumbersome to retrieve in the future. Accordingly, before you archive, you could create summary sales reports that you can access in the future.
Another important strategy might be to convert your archived data out of proprietary formats. If you need to access your archived data in the future, the database system that you are currently using may no longer be supported. Accordingly, you want to make sure that the stored data is in a portable format that can be easily imported into other systems. Examples of portable formats include comma delimited, JSON and XML.
Finally, if your archived data contains sensitive information like trade secrets, or financial details like credit card numbers or financial statements, you should consider encrypting the information before committing it to long term storage.
Data Archive File
Data archive files can be created using a variety of methods. There are infrastructure as code platforms like “Terraform” that let you automate archiving by creating sets of instructions which execute to automatically create, compress and save archive files.
Large scale database systems like SQL server have built in archiving features that will extract data based on specific criteria that you specify. This extracted data will be put in a staging database where it can be saved to a backup file.
In our own Tracker Ten system, creating data archive files an be performed using our institutive user interface. You simply need to use the search functions to select the items you want to archive and you can save them to a separate file. You can then remove these records from the original database. You may also want to ZIP the separate file to save storage space.
One thing to keep in mind when creating your archive file is that your database may just store pointers to large items like photos. In this case your archive file may just contain pointers to these larger data items, instead of the actual data items themselves. In this case you will need to find out where your database stores these types of auxiliary files, and you may need to make a copy of the auxiliary files manually.
Once you have created and compressed your data file, you can store it on any time of media including hard drives, optical disks, USB drives etc. You may also elect to save your data to a cloud-based service. If you are concerned that you may need to retrieve your data many years into the future, you should make sure that your selected media will last that length of time. Not all media is created equal.
Accessing Archived Data
What happens if you realize that you need to access data that you have previously archived? As long as you have a copy of it, you should be able to access the information again. Typically, the process would involve finding the archive file (this is why it’s important to properly name archive data files), and uncompressing it and decrypting it if needed. Then you should be able reimport the archive data into a new database where you can parse and retrieve information as needed.
Data Retention Regulations
Depending on the industry you are in, you may have a legal obligation to keep your archived data for a certain length of time. For example, if your company does drug trials, the FDA will mandate a length of time you need to keep all associated data. When you are archiving information it’s always a good idea to research any rules and regulations that may apply to your industry.
