SQL script to restore WordPress backup localhost

I needed a simple SQL script to help me restore WordPress backup localhost to ensure that I didn’t have to go through the pain every time that I wanted to bring my data to my local server. This worked really well and all you need to do is search and replace on local.blog to the name of your localhost.

-- update site
UPDATE wpdb.wp_site
   SET domain = 'local.blog'
 WHERE id = 1;

SELECT * 
  FROM wpdb.wp_site;

-- update blog details
UPDATE wpdb.wp_blogs
   SET domain = 'local.blog'
 WHERE blog_id = 1;

SELECT * 
  FROM wpdb.wp_blogs;

-- update site meta
UPDATE wpdb.wp_sitemeta
   SET meta_value = 'http://local.blog'
 WHERE meta_key = 'siteurl';

UPDATE wpdb.wp_sitemeta
   SET meta_value = '127.0.0.1'
 WHERE meta_key = 'dm_ipaddress';

SELECT * 
  FROM wpdb.wp_sitemeta
 WHERE meta_key in ('siteurl','dm_ipaddress');

-- update options
UPDATE wpdb.wp_options
   SET option_value = 'http://local.blog'
 WHERE option_name in ('siteurl','home');

SELECT * 
  FROM wpdb.wp_options
 WHERE option_name in ('siteurl','home');

-- update posts
UPDATE wpdb.wp_posts
   SET guid = REPLACE(guid,'blog.david-jensen.com','local.blog');

I have gone through each of the tables above that need updating, you could probably get away without updating all of these but I figure that as it is a script you might has well do it properly. I have also included the select statements afterwards to ensure that you can see the value has changed.

Music to write this code to

Erol Alkan classic is something super chilled to sip your tea to for the calm before the development storm once you have your local restored.

Leave a Reply

%d bloggers like this: