Story About Bad Backups

Backups are typically only considered when you need them. That’s generally not the best time to find out if the backup is any good. Any good backup system also involves some trial runs to make sure the restores work. If you can’t restore from your backups, then you don’t have a backup.

No one is immune to mistakes

I migrate websites regularly. To date I estimate I’ve handled over 500+ site migrations. While I’d like to say all have been 100% without any issues, there was one migration which I nearly lost everything. No matter how much experience you have or how diligent and proactive you are, mistakes can happen.

My story begins, like most, moving a WordPress website from an old server to a new one. The migration to the new server was flawless, DNS switchover flawless, shutting down the old server, that’s were things went down hill.

After I shutdown an old hosting server, I typically check to make sure the old site is taken offline. When I looked, the website was still running. First thought, that’s strange and then quickly moved on not thinking much about it. Then I get an email from the client. Started off something like this.

 “Everything with the website looks good except for one link. It’s important that we get it working as we use it daily.”

As soon as my eyes saw the link my mind starts to connect the dots. That link was going to a subdomain, not the WordPress site I just moved. The fact that old server is still serving the website is probably due to the fact I closed down the wrong web server. I pull up the link in my browser and I see the dreaded This site can’t be reached. I quickly pull up the Internet Archive snapshot to see what I accidentally deleted. Yep that site doesn’t look like anything I was working on. I reach out to the web host provider only to receive the bad news. All server deletions are final and immediate. That includes any backups.

I sent a very uncomfortable email to the client and explained what happened. I also asked for them to tell me more about the server and any info they had on the website it was hosting.

Not all backup products are created equal

So here I am in a situation where a server I was unaware of was just gone. After multiple talks with the host provider I discover the client was paying for an offsite backup managed by the hosting provider. It took nearly a week and half before they could place the files on the new server for me to work with.

The good news was the backup contained all files from the server. The bad news, there was no database backup only the database files. Lesson learned, all backups should have a database dump. If your not familiar, MySQL stores it’s raw files in a data directory which looks something like this.

Recreating database from raw files

Restoring from these raw files are very difficult but not impossible. I knew right away from a past data restoration experience that I could either pay someone $1000 to get the data back or lock myself into learning mode and attempt to do it myself. Since the mistake was mine, I decided I’m attempt to do this myself. So begins the my data restoration.

Restoring database locally

Assuming you have a computer running the same operating system and version of MySQL you might be able to get things running by shutting down MySQL, moving all of the files you see above into the MySQL data directory folder and restarting MySQL. For my situation, that failed with lots of errors when I attempted to start MySQL with the foreign files. Another thing you can try is copying just the database folder into your computer. When I did that, I could see all of the tables however it gave me various table not found when I attempt to see the contents. My process required a lot of learning. These were ultimately the steps I took to successfully restore data locally.

  • Created a new empty database locally, named anything.
  • Used an online service to recover my table structure from my .frm files
  • Added in row_format=comcact to the end of my recreate table queries as per to fix the issues described in this article.
  • Walked through these steps to trick MySQL to load data from the original .idb files. See actual .sql statements I generated to accomplish this part.

Always restrict before deletion

One thing which could have helped smooth this situation is if the hosting provider took the policy, restrict before delete. Customers don’t know what hosting products do much less what it’s going to affect if removed. By simply allowing customers to cancel there service with a limited a number of days to restore it would be super helpful.

For that reason when a hosting customer wants to terminate their plan with my service I now make the site inaccessible. A few weeks later I actually delete the site. This buffer of time can saves me loads of time. If there is something the customer missed they’ll discover it the moment the site is inaccessible. Things like, “Where did my email signature go?” or “I forgot I had XYZ file on my site..” all easily solved if the data isn’t actually removed.