Today I just had the terrible experience of having a database lose data, need to restore, only to not have a recent backup. If you haven’t had this experience before, please, take this serious. My wife was home for lunch as it happened, and she watched as the blood drained from my face. It only took a few seconds for the loss to happen, and immediately I knew exactly what the repercussions where. The immediate second thought that passes through your brain is “Where are my backups?” That is when I realized I didn’t have my nightly backups set up on this server. I quickly checked the file date on the last known backup I had.
It could have been a lot worse, but it was still extremely bad. Those last thirteen days had been record setting days. Emails each day were going around about record new signups, records internal messages sent, etc. Those thirteen had been the best 13 days by far.
If some of you are wondering what had happened, and know me to be very diligent in my backups, I did the one wrong, terrible thing: I made an assumption. I’ve blogged before on how backups have saved me in the past, and how I am almost a fanatic about them. So what the heck happened?
This website was on some hardware that was starting to get overburdened. Then, out of the blue, our traffic exploded and our web server and database server started to grind to a halt. I spent long hours and sleepless nights migrating from these old servers from a terrible host to some new virtual machines. We then discovered our MySQL Database was so intense that the virtual server couldn’t handle the CPU and I/O requirements. Finally, in a last attempt of desperation I moved the Database to a spare box of another company who gave me permission to use it temporarily. That finally worked and allowed us to handle the load on our Database. By the time I finished this, it was about 8 AM in the morning and I went to bed.
I assumed we’d only be on this box for a day or two, so I didn’t setup the backup scripts. However, it gave us more breathing room than we expected, and other issues came up non-db related. The company lending the us the server said we could take our time, so the urgency on ordering our new hardware was pushed off more and more. I had completely forgotten about setting up backups scripts, and we ended up where we are now.
What I’m Changing Personally
I’ve decided to make two changes personally after this experience.
First, there are zero excuses for not having automated backups. Zero, zilch, nada! If a backup should have occurred, there is no excuse for it not to happen.
Secondly, I’m going to pick a day of the month where before I do anything else, I verify that all the backups are working. My father-in-law on the first business day of the month has the habit of doing his business’s billing and other accounting activities. He lets just about nothing stand in the way, and all ways checks his bank accounts and records to make sure everything is in order. I’m going to adopt this same idea, only with servers and data. The first business day of the month I’m going to go through all the servers under my care, verify the backups are working, check error logs, etc. I want to catch the problem before anyone else does.
How To Prevent Data Loss
Here are a few guidelines to make sure you don’t fall victim to data loss.
- Select a Backup Schedule & Follow It 100% – I suggest for most websites, a daily backup will work out pretty well. If you have a lot of data that would really stink to lose that changes frequently through the day, you could backup several of the tables hourly.
- Back Up To Several Locations – I like my servers to have two hard drives. One for the live data and another for backups. Then, after a backup has been created, I like to sync that backed up data to another server. It is important that if a meteor fell from the sky and hit your data center (or a flood, fire, earthquake), you would have a very recent backup somewhere else.
- Verify Your Backups – I can’t stress this enough. After this terrible accident of not having a recent backup, I went and checked all my other website database backups. I found out that one critical database’s backups were broken and not running nightly. You never want to find out this information after you have to restore from a backup. Regularly verify that your backups are being created, and that you can restore from them.
Hopefully this will motivate at least one person in our profession to evaluate their backup strategy and make it better. You don’t ever want to tell a client that you just lost 13 days of their record setting work.