Partial Database Restores From a Backup

So maybe you accidentally deleted 600,000 customer records and associated usermeta from a WordPress site. Or maybe I did. Regardless, it’s a problem and they now need to be restored.

Thankfully, there’s a backup (boy howdy, you better hope so). Our site is hosted with WP Engine, so we restored a snapshot from before the accidental deletion to a new environment.

We hadn’t deleted all the user records, just a subset. But the easiest way to restore them from the backup was to export the all the users and usermeta within a specific range- but then only restore the ones that were missing. We did this using WP CLI.

This is how we exported a partial user table:

wp db export user-range-1.sql --tables=wp_qftw_users --where="ID > 200000 AND ID < 600000" --insert-ignore=true --no-create-info=true

This is how we exported a partial usermeta table:

wp db export usermeta-range-1.sql --tables=wp_qftw_usermeta --where="user_id > 200000 AND user_id < 600000" --insert-ignore=true --no-create-info=true

The "--no-create-info=true" flag is important! This tells MySQL not to drop the existing table before re-importing. Otherwise you would be deleting all the existing users on your site instead of just restoring the missing ones.

Now, you'll need to get those .sql files onto your production site and run the following to restore the users and usermeta that is missing.

wp db import user-range-1.sql --insert-ignore
wp db import usermeta-range-1.sql --insert-ignore

The "insert-ignore" flag allows us to import only the users and usermeta that don't have an existing ID on the site. Existing users and usermeta will not be touched.

This would also work for restores of other types of data, like orders or products or posts- as long as you're correctly restoring data from all the tables that were deleted, and assuming that your IDs are unique between the backup and production.

Best of luck if you find yourself in this situation!

About Devin

I am a developer based in Austin, Texas. I run a little theme shop called DevPress and help manage a WooCommerce shop with Universal Yums. Find me on twitter @devinsays.

Leave a Reply