Daily backups are a necessity for any website. However performing a full site restore from backup should only be considered as a last resort. For high traffic WooCommerce sites restoring a backup means all customers orders are lost from the backup time till the restore time.
Avoid performing a full site restore if possible
A customer recently reached out to me to perform a full site restore on a WooCommerce site. This particular site receives 30+ orders per day and restoring would have removed potentially 2 full days of orders. I recommend to do a partial restore in order to preserve all user accounts and customer order.
All products were corrupted and needed restored.
Apparently a 3rd party integration wasn’t configured correctly and ended up breaking pricing info for over 1000 products. Worse yet, many products had bad info or were duplicated. It was a mess. Restoring all products was the only way to insure nothing was missed.
Manually restoring just the parts of the database relating to WooCommerce products
WooCommerce stores product info within the database tables wp_posts and wp_postmeta. Start by restoring a database backup locally in order to extract only the WooCommerce products. Using Sequel Pro run the following 4 separate SQL selects and then copy them into files named posts-products.sql, postmeta-products.sql, posts-product-variation.sql and postmeta-product-variation.sql using the “Copy as SQL Insert” option as shown in the screenshot.
SELECT * FROM `wp_posts` WHERE post_type = 'product';
SELECT * FROM `wp_postmeta` WHERE post_id in (SELECT ID FROM `wp_posts` WHERE post_type = 'product');
SELECT * FROM `wp_posts` WHERE post_type = 'product_variation';
SELECT * FROM `wp_postmeta` WHERE post_id in (SELECT ID FROM `wp_posts` WHERE post_type = 'product_variation');
Before any restoring can take place, all existing WooCommerce product-related data needed to be purged. The following SQL statements will do just that. The order is important otherwise there will be orphaned database records.
# Removes all postmeta for products variations
DELETE FROM `wp_postmeta` WHERE post_id in (SELECT ID FROM `wp_posts` WHERE post_type = 'product_variation');
# Removes all product variations
DELETE FROM `wp_posts` WHERE post_type = 'product_variation'
# Removes all postmeta for products
DELETE FROM `wp_postmeta` WHERE post_id in (SELECT ID FROM `wp_posts` WHERE post_type = 'product');
# Removes all products
DELETE FROM `wp_posts` WHERE post_type = 'product';The last part is to import posts-products.sql, postmeta-products.sql, posts-product-variation.sql and postmeta-product-variation.sql into the production database. With that completed, all WooCommerce Products should now be restored.