Importing WordPress attachments into Media Library

If you are reading this, you probably know that WordPress doesn’t handle very well exporting media attachments unless you choose to export “All the content” with the WordPress Export tool.

Lot of users don’t need to import all the content and just choose to import Posts using the xml importer / exporter tool in WordPress and there’s where the problems starts.

A year ago I built a plugin that let users export featured images  and then import them using WordPress importer tool because I needed for a client, and today I faced a very similar problem with another client but this time with all images attached to posts.

Basically my client developed a new theme on a new server, and imported just blog posts. He worked on the posts , make the theme live, moved old site to a folder on the server and used it normally.

After some time he realized that over 10k+ images that were in the media library were not there any more. So how can we import just media attachments into media library and WordPress?

Please be aware that this migth not work in your particular scenario, so be sure to backup everything before your start, Im not taking any responsability!

So here is what I did:

I used phpmyadmin, but you could use any tool that you feel confortable with.

First of all I did the following SQL query and then exported the results into a SQL file:

SELECT * FROM wp_posts WHERE post_type = 'attachment' AND post_parent != '0';

Be sure to change the table prefix to match yours. As you can see Im only retrieving attachments that are actually attached to something.

Then I did :

SELECT * FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'attachment' AND post_parent != '0' );

Once all the postmeta is exported is time to modify the sql files. I use Sublime text editor for coding and it handles very well large text files ( mine was around 40mb )

In the wp_posts.sql file I did the following:

• First I change all the INSERT INTO sentences to INSERT IGNORE INTO in case duplicate keys exists It don’t break them
• I modified all image urls to match new domain (not always needed, depending where you are importing from)
• I removed all unnecessary SQL code such as ALTER TABLE and INSERT TABLE and just left the INSERT INTO SENTENCES
• Zipped the file and imported into new site db with phpmyadmin

In the wp_postmeta.sql file I did the following:

• I removed all the unnecessary SQL code such as ALTER TABLE and INSERT TABLE and just left the INSERT INTO SENTENCES
• I changed all the INSERT INTO `wp_postmeta` ( `meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES to INSERT INTO `wp_postmeta` ( `post_id`, `meta_key`, `meta_value`) VALUES . Basically Im removing meta_id because I want to insert all the postmeta at the end of the table and use the auto increment
• I did a regex search and replace to remove all ID from values. I used \( ([0-9]+), and replaced with just (
• Zipped file and imported to new site

The last and most import step is to copy the image files into the wp-content/uploads/. I did this directly on the linux console with a simple cp -n. Other might use rsync which is quite good also. I did something like:

cp -n OLD/wp-content/uploads/2013/08/* wp-content/uploads/2013/08/

This command just copy over files that don’t exist and ignore (don’t update) the ones with same name.

And voila! Everything was working again. Old the images where attached to the original posts, etc.

Again be careful and backup everything before trying this!

If you need help with your site , check out wordpress support plans!