Recently at work we had a situation where we needed to roll back to the previous day's backup of our WordPress site to undo some changes that went awry. However, since posts had been added and modified since that time, we also needed to restore those posts (and associated meta records) after we rolled back. Here's one way to do that.

1. Clone the database in its current state

First of all, we need to clone the database in its current state so that we don't lose any work that was done since yesterday's backup. Then later we can compare this clone to yesterday's backup to determine which posts to re-import.

Create an empty database for the clone:

CREATE DATABASE my_database_clone;

Then export your current database and import it into the clone:

mysqldump -u username -p my_database > my_database_clone.sql
mysql -u username -p my_database_clone < my_database_clone.sql

 2. Roll back to a previous backup

Locate yesterday's backup file (or whatever point you want to roll back to), and import it into your database:

mysql -u username -p my_database < previous_backup.sql

 3. Identify the posts to restore

Write a query to identify the posts you want to restore from the clone. In this example I'm selecting anything modified after the date I rolled back to:

SELECT *
FROM my_database_clone.wp_posts
WHERE post_modified >= '2014-02-10'
ORDER BY post_modified DESC

Adjust your query as needed to select just the posts you want.

4. Insert them from the clone

Now, using that query, we'll re-insert them from the clone into our database.

INSERT IGNORE INTO my_database.wp_posts
SELECT *
FROM my_database_clone.wp_posts
WHERE post_modified >= '2014-02-10'
ORDER BY post_modified DESC

The IGNORE keyword suppresses any duplicate-key errors that would cause the statement to abort. See the documentation on INSERT Syntax for details.

5. Get associated meta records too

We're almost there. Now we have the posts that were modified since the rollback, but we also need to get the associated post meta records. First select them to make sure we know what we'll be inserting. Here I'm selecting everything from both tables, so I can see which posts I'm dealing with:

SELECT *
FROM my_database_clone.wp_postmeta m
INNER JOIN my_database_clone.wp_posts p ON p.ID = m.post_id
WHERE post_modified >= '2014-02-10'
ORDER BY post_id, meta_id

Now just add the INSERT statement and select only from the postmeta table to insert them:

INSERT IGNORE INTO my_database.wp_postmeta
SELECT *
FROM my_database_clone.wp_postmeta
WHERE post_modified >= '2014-02-10'
ORDER BY post_id, meta_id

That should do it.