Restoring old WordPress posts one SQL table at a time [Video Tutorial]

by Ka Edong on June 13, 2012

I’ve prioritized many other things the past years. And I haven’t been taking good care of my blog for some time now.

When I started fixing Technobiography.com a few weeks ago, I realized there were many things that were broken.

Most major was not being able to login to my wordpress dashboard.

After many attempts finding solutions, I decided to build my blog from scratch. I made sure that my old posts were somehow backed-up in some form (SQL!). I decided that I’d be able to find a way to restore my old posts from way back 2004.

Thanks to my good friend Ric Vinas, I was able to restore my blog from my SQL backup file. Ric created a video to illustrate how to do it.

Here’s a summary of how I restored my old blog posts from a gigantic mySQL backup file.

  1. Backup. I used this guide to create my backup SQL file.
  2. Re-install fresh wordpress. I made sure to drop the old SQL tables via dreamhost’s mySQL. This is where Ric’s video tutorials came in. I had to tweak the procedure. I’ll speak to that after the videos.
    Part 1: 
    Part 2:
  3. Since I’m using Mac, I downloaded TextWrangler for editing my  SQL  file.
  4. I basically did most of what Ric showed, including the find-replace of the “wp_” table names.
  5. Here’s where I diverged. When I run my entire SQL backup, I would be back in square one where I couldn’t access my dashboard. What I did was to restore individual tables.
  6. Here’s my sequence for restoring tables:
    1. wp_posts
    2. wp_comments
    3. wp_commentmeta
    4. wp_postmeta (these meta tables were results of some comment plugins where visitors can subscribe to posts)
    5. wp_terms
    6. wp_term_relationships
    7. wp_term_taxonomy
    8. wp_links
      I restored individual tables by capturing the “Drop Table if exists”, “Create Table if not exists” and the “Insert into wp_posts” portions of the SQL file. I pasted these codes in phpMySQL on dreamhost.
      Here’s a screenshot of the portion of SQL code that I’d capture:

      Restore wp_posts table

      Restore wp_posts table

  7. I made sure to NOT restore my wp_users, wp_usermeta table. The source of my blog problems started in those tables.
  8. At this point, most of my critical work was done. I had recovered my old blog posts AND I was able to login nicely in wp-admin.
  9. A little icing on the cake: I restored also three of my more recent blog posts which I wrote via the fresh wordpress install. I could have restored it via my dashboard (new post), but I kinda did it the harder way — I restored it using SQL! hahaha! I could have just cut-paste my three posts … hey, we like adventure, don’t we?

Cleaning up:

  1. There were a couple of duplicate categories. I cleaned that up via the dashboard.
  2. I also imported some SPAM comments, I just deleted them via dashboard.
  3. I had some table columns that came from some plugins (wp_commentmeta / wp_postmeta). Those plugins I’m not planning use anymore . I decided to just keep those excess columns anyway.

That’s it! I hope this documentation will be helpful for other people.

Here’s what Technobiography looks like after my restoration. Tadaaaah!!!!

Technobiography is Back (Again!)

Technobiography is Back (Again!)

 

Comments

  • I do have some blogs but they are more or less brochure blogs, the simple functions that I execute in the them are writing posts and adding images and things like that, I have recently started to have affiliate products also, I want to take this work full time, These SQL queries were really difficult to understand!

    • Hi Molly,
      Thanks for dropping by. Hopefully, you won’t need to understand SQL in the near future. I didn’t understand them either! LOL!
      Cheers!
      edWIN

Previous post:

Next post: