Restore selected posts from a backup
February 14, 2014
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
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.