Recover deleted WordPress posts by Author (without a plugin)

R

A customer of ours recently removed unneeded “Administrator” users from WordPress, which is good practice. During the process, he selected to delete their content – not realizing they had created posts. Good news! You can recover these if you have a backup, and you can even restore them to a different author – without a plugin.

Restore Backups To Previous Date & Import/Export Plugins

Yes, we could have restored the site to a previous day when that user existed and then re-deleted the user but changed the author. In this case, that wasn’t easy because this is a very active membership site. Restoring from backup would overwrite any member progress from the past few days.

In theory, this was the right thing to do, and there are a few plugins that enable you to export XML from posts based on any author. We didn’t want to use a plugin — because we LOVE to keep WordPress sites as clean and lean as possible. We decided to use WP-CLI, and I have to say if you ever need to do any maintenance like enable or disable plugins or import or export posts – and more, it’s a fantastic CLI tool that surprises me every time I have to use it, kudos!

Hosting suggestions

We found it challenging to restore WordPress quickly to a point in time – from a backup at the hosting provider. When you shop for a web hosting provider, I suggest finding one that allows you to do that – and quickly. You should almost always have a staging site. Test changes there first. For WordPress, I suggest checking out Kinsta hosting.

Restoring Database and Files

Because of the immediacy, I opted to restore the site to a droplet on DigitalOcean, with WordPress pre-installed. The choice had additional benefits. I could copy files directly from the current host. That allowed me to avoid downloading a 25gb backup to my local machine.

With the backup of the website files and database copied to the DigitalOcean droplet, I extracted and moved the restored folder, replacing what was currently a blank WordPress site. Reset the user and owner with chmod etc.

This entire process took less than 30 minutes. You should be somewhat comfortable on the Linux command line, with commands like tar, wget, MySQL-CLI and mysqldump.

One small problem when restoring the database. I received an error.

Error: Invalid default value for 'user_registered'

The solution was to add the following to the top of the SQL file I was importing.

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

Tech Documentation Scares Me! When something fixes a problem, we (I) don’t love taking the time to understand it – well enough to teach it. I don’t think this is all my fault. Tech people make things too hard sometimes – even the smartest people I know use Google. When you CAN take the opportunity to learn more – do it!

Read more about Server SQL Modes but be sure to check the reference for your specific version of MySQL or MariaDB.

Another thing you must do for WordPress to work correctly is to update the domain name in the database. For this, I used the MySQL client and SQL below to update the wp_options table with the domain name I chose to use for the restore. If you prefer PHPMyAdmin, that works perfectly fine and a bit easier.

UPDATE wp_options SET option_value = replace(option_value, 'http://old.domain', 'http://domain') WHERE option_name = 'home' OR option_name = 'siteurl';

Exporting Posts (without a plugin)

I installed WP-CLI on the DigitalOcean Server. To see the plethora of options for exporting just try this:

wp export --help

Exporting with WP-CLI

I ended up with the command below. I wanted to export into a folder so I created that first, navigated there then exported right into the directory. I also wanted to name the file specifically. This worked beautifully!

wp export --post__in=12345,67890 --path=/var/www/html --filename_format={site}.wordpress.{date}.{n}-myexport.xml

Importing with WP-CLI

The import needed to replace the deleted user with an account that did exist on the live website. WP-CLI allows you to use a CSV to define the old and new Author. This is amazing!

wp import domain.wordpress.2020-09-24.000-myexport.xml --authors=mapping.csv --path=../domain.com/html/

And with that, the posts were restored. Luckily for me, these files didn’t have any images embedded but if they did the — you guessed it, the WP-CLI can export those too the help command tells you how. In summary – restoring posts using WP-CLI was a fun task, took very little time, and gave the customer exactly what they wanted without installing a plugin – keeping their ecosystem clean!

We love clean ecosystems and WordPress. If you need help getting your WP site in good working order or restoring or moving posts we can help. Use the contact form on our website to reach out! Thanks for reading!

About the author

Jon Cavanaugh

Add comment

By Jon Cavanaugh

Recent Posts

Recent Comments