Database Backups: A Safety Valve for Maintaining Your Records
In a recent post, I discussed the Benefits of a Test Environment. While having an environment to test processes that cannot easily be undone is ideal for maintaining data, database backups are also an important safety net every organization should have.
Regardless of whether or not you have a test environment, your backup and recovery process is a critical piece of your organization’s technology puzzle. I acknowledge this is a dry topic, but as one author put it, “…[backups] can mean the difference between a slight computer setback and living through your own electronic apocalypse.” My hope is that this article will help you open a discussion with your IT resources regarding your database backup and recovery process. So grab a glass of water (or your beverage of choice), and read on!
Let’s start with a quick Q&A and a show of hands…
Q: How often should you back up your database?
A: Only as often as you can afford to lose the work you’ve done in it since the last backup.
Poll: Raise your hand if you’ve had your computer at home experience a failure. Now, raise your hand if you’ve lived through the failure of a critical computer or database at work.
Is there anyone out there that didn’t raise a hand? I suspect there is not many as most of us have been through computer failures. When you think back on those experiences, what made the situation better? Or perhaps worse? Was it having (or not having) a viable, recent backup of the data on the machine that failed?
Generally, the acceptable interval between database backups is 24 hours. While this schedule may be acceptable for your organization most of the time, it may not be acceptable, or even wise, all of the time. There are times when you should consider performing additional backups. Your IT resources will be familiar with this concept, but they may not realize your specific database(s) has actions performed that would warrant additional backups.
So when should you consider performing additional database backups? A good rule of thumb is to consider performing a backup of your database any time you are going make a change that impacts a significant percentage of the records in your database, and that can’t easily be rolled back (undone). In the case of the database for Blackbaud’s The Raiser’s Edge (RE) and Blackbaud’s The Financial Edge / The Education Edge (FE/EE), there is no option to roll back changes so once you make them they are permanent, and there is no way to get back the previous data values without restoring a backup.
If you are manipulating large amounts of data via ImportOmatic, List Management, SegmentOmatic, the import function for RE or FE/EE, or the Global Change facility in RE or FE/EE, you should give serious consideration to having a backup performed immediately prior to launching your process. You should then immediately check your work and decide if you need to have your backup restored. If you decide you need the backup restored, first stop all entry into the database and advise users to document what they had done since the additional backup was performed as they will have to re-do this work after the restore. Then, contact your IT resources. If you had the backup performed right before you launched your process, you probably will not have lost more than an hour or so of work. That may sound like a lot but it is actually a small window, by comparison, if you launched the process in the afternoon and users had been working in the database all day.
The other critical piece of a backup and recovery process is the regular testing of the backups that are being made. We tend to take our backups for granted, thinking of them like insurance policies. But unlike an insurance policy that is assuredly there as long as the premiums are paid, backups have to be regularly verified (tested) to be sure they will be there when you need them. Backups are often performed by automated software, and the report given is usually only an indication of whether or not that software thinks the file copy happened successfully. A positive report is not necessarily a reliable indication of whether or not the backup is actually viable. Only testing can determine viability. This applies to backups made in-house as well as those made via a cloud service such as Carbonite, Backblaze, or CrashPlan.
Testing backups usually happens after hours, and it requires IT resources to accomplish. I can’t stress enough its importance. The testing process is fairly simple and is the same process that would be performed in the case of an actual emergency. So, it is also very good practice for the resource responsible for restoring backups. When the database is first stopped, all users must exit the application(s) that access the database, which is why this testing tends to be performed after hours. Then, the live data is set aside and the backup copy of the database is put in place (restored). Finally, the database is re-started. Generally, one user also accesses the application(s) to verify the data from that side. If the backup fails to restore properly, the restored database will not start, or the application errors upon access, then that backup is not considered to be viable.
Such a failure should trigger an immediate, manual, backup of the live data be performed. It should also trigger an investigation into the cause of the failure, and backups should be manually performed until the cause of the failure is located and fixed. Then, the fix should be verified by the repeating the testing process that exposed the failure.
When you actually need a backup is not when you want to discover there was a problem with your backup process. Imagine losing everything you had done in your database for the last year. Imagine discovering you have NO viable backups and your database is just gone. I’ve seen both of these happen over the course of my career, and they are indeed electronic apocalypses. The interval for testing backups is something each organization decides on based on its individual needs.
I do not mean to scare you with this post. Actually, I do mean to scare you just enough to inspire a healthy respect for the importance of your organization’s backup and recovery process. Hopefully, you will never need your backups, but if you do, my hope is that you have a solid process in place that makes the situation only “a slight computer setback.”