I just duplicated this site so that I can test out new versions of WordPress and test plugins and themes that either I have written or that have been written by others with real content.  It took longer than I had hoped, but it still turned out to be a straightforward procedure.

I started by using the Export functionality built-in to the WordPress admin panel.  When I imported into the other site, I noticed a lot of things were missing.  Very few of my posts had been imported and none of my links were there.  I’m rather disappointed with this feature.  It seems extremely unreliable.

The next thing I tried was to back up the database using a plugin called WordPress Database Backup by Austin Matzko.  It probably would have been just fine for what I wanted, but I was unsure about how to restore it so I ended up exporting the database from my phpMyAdmin panel.  One thing I didn’t know with the backup file produced by the plugin is if it referred to the database directly.  When I looked at the backup file produced by phpMyAdmin, the only reference to a the database was in a comment.  Once I selected the database for the other site, all I had to do was to choose import and select the database file.

However, there are still a few things that need to be done.  I could navigate to the new site, but since it didn’t have the same URL, I couldn’t log in.  To fix this, I had to replace all instances of the old URL with the new URL.  I had hoped to just use the Velvet Blues Update URLs plugin, so I first just changed the siteurl and home options in the wp_options table.  Once I logged in, I tried to activate the plugin, but it kept sending me to an error page.  Bummer.

After a little research in the WordPress.org forums, I found the solution.  It turns out to be extremely simple, so I might try to build a plugin to do it.  All that has to be done is to execute a set of SQL commands for each table/column that might contain the old URL.

UPDATE wp_options
  SET option_value =
    REPLACE(option_value, 'C:\\\\Inetpub\\\\OldFileLocation', 'C:\\\\Inetpub\\\\NewFileLocation')
  WHERE LEFT(option_value, 2) <> 'a:';
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE LEFT(option_value, 2) <> 'a:';
UPDATE wp_posts SET guid = REPLACE(guid, 'http://www.old-domain.com', 'http://www.new-domain.com');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

Note the WHERE clauses above – they exclude option values that are stored as arrays.  Unfortunately if you simply replace the values without fixing up the array lengths, PHP won’t be able to read the values properly, so these must be fixed up manually through the proper options page.  Also note the quadruple backslashes.  A backslash is an escape character, and the unescaping logic is apparently done twice, so you have to include 4 for each 1 you want.

Once the updates were done, I just had to make a few minor tweaks, such as not publishing the new site to Google, etc., and to make sure I hadn’t missed any settings and then I was good to go.

Final note – I messed up one of the above queries and wiped out all the post content, so I had to start over.  This time I used the backup file produced by the WordPress Database Backup plugin and I was able to import it with equal success into the phpMyAdmin panel.