Periodically I need to copy the contents of a log to another domain for testing purposes.  WordPress doesn’t make this particularly easy as it embeds the complete URL of the site in many places, including the options table, in the posts table for each post, and even in posts, such as when media is added.

There is no need to create the blog in the new database. If you’ve already done that, I would recommend deleting the tables before continuing.

Before you move on, you’ll want to back up your blog.  There are plenty of solutions for this, but a handy one that I’ve been using is WordPress Database Backup by Austin Matzko.

Now you can import the resulting backup file against the destination blog’s database.  I use phpMyAdmin for this, so here are the steps for doing that:

  1. Log in to phpMyAdmin.
  2. Select the destination blog’s database.
  3. Click the Import tab.
  4. Click the Browse button and find the .sql file that was the result of the backup operation.  If your backup produces a .zip or .gzip file, that will work fine also.
  5. Click the Go button.

You now have your content over on the new site, but you still have work to do.  You still need to change all references to the old domain to be the new domain.  There are three SQL commands you need to execute to do this.  To execute a SQL command, select the database in phpMyAdmin and click the SQL tab.  Paste the following commands into the edit box and change old-domain and new-domain as appropriate.  If you changed your table prefix, don’t forget to change the names of the tables as well.

First let’s fix the wp_options table.

UPDATE wp_options SET option_value = replace(option_value, '', '') WHERE option_name = 'home' OR option_name = 'siteurl';

Now let’s fix the guid column in the wp_posts table.

UPDATE wp_posts SET guid = replace(guid, '','');

Finally let’s fix the content in the wp_posts table.

UPDATE wp_posts SET post_content = replace(post_content, '', '');

The final step will be to copy any files that were uploaded, such as in the wp-content\files directory.