This is a story of momentary triumph, devastation and recovery. It was a collaborative effort but the real hero is Nathan Jeffery who you can read more about at the end of this post (so please keep reading).
I had two sites a few days ago which I decided to merge into one. The one site was a portfolio site at one of my domains and the other was my long standing blog (this one, more or less). I initially thought it would be a good idea to have a standalone portfolio site, distinct from my blog and other sites but the more I thought about it, the more sense it made to rather combine it with my personal blog and create a single personal site with an expanded portfolio section and the blog posts.
I also decided to move my personal site from my pauljacobson.org domain to this one. A .org domain is more for NGOs than people but it was available at the time and seemed to work. I found a WordPress Codex article titled “Moving WordPress” which describes how to move your WordPress site from one domain to another with options for moving on the same server (which I was doing) and across servers.
The instructions are a little technical but I followed them (mostly) and was able to move my blog to this domain fairly easily. It worked well but I forgot to do a couple things with the old site to facilitate an easy transition across to the new domain. The portfolio site was a couple pages so I just exported the pages from that site and imported them into the new site and I had a working site by midnight on Saturday night.
On Sunday morning I decided to uninstall the old site’s WordPress installation through the Hostgator backend and even though the wp-config file in the old directory that contains database details was modified to a different database, the old site’s configuration seemed to maintain a link to the database the new site was using. The way I moved the site, both the new site at this domain and the old site at pauljacobson.org were sharing a database. When I uninstalled the pauljacobson.org WordPress installation, I deleted that database and not only undid all my work the night before but also lost the only coherent version of my blog.
My only thought for about an hour isn’t fit for publication. To make matters worse, I didn’t think to backup my site or the underlying databases and Hostgator doesn’t automate backups unless you configure that somehow.
What I did do was export the WordPress XML file from within WordPress and I had similar export files from 2013, 2010 and a little earlier. Unfortunately when I imported the export files I had, I found that a lot of very old posts suddenly had publication dates for when I did the import, in other words for Sunday. That didn’t work at all for me. There were too many posts to go back and manually edit the dates.
Fortunately, I had started chatting to Nathan Jeffery about the migration (we chat now and then about technical stuff) and he was helping me redirect the pauljacobson.org site to this one using edits to my .htaccess file (don’t worry if that means nothing to you, it isn’t really relevant to the story).
So, on Sunday morning I messaged him with what began a very long process of recovery:
Right, everything was working pretty well (the redirect hadn’t kicked in yet) and then I deleted the old WP installation and it too the database my new site was relying on with it
I at least had the foresight to download a version of a database I was working with for the migration but, when I tried to import that into a new database for the new, gutted site, I received errors from phpMyAdmin:
Nathan then began the process of saving my site. I sent him the SQL database and he did some magic to it and returned a database which phpMyAdmin rejected, for a different reason:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w’ at line 1
It turned out there was some HTML stuff at the end of the SQL file which Nathan removed and we uploaded to phpMyAdmin and it accepted it. Two things happened at this stage:
- I only added about a third of my blog’s original posts; and
- I didn’t have a user in the database and couldn’t login (the database has sets of entries for posts, comments, users and for some plugins and the database we uploaded didn’t have user details for some reason).
I exported user info from another database I had on the server and that gave me access to the site’s admin section. At this point the site was up but I didn’t have most of my blog posts, just a couple WordPress XML export files that didn’t work properly because the publication dates for a lot of the old posts were changed to yesterday’s date (when I imported the file).
I used an old WordPress XML file to add posts up to 2011 and then started working on the date issue in those old posts. Nathan involved one of his coders in the problem and I spoke to Adii Pienaar (one of SA’s top WordPress people who founded WooThemes). He thought it may be a server issue so I opened a support ticket with Hostgator. I only received a response from Hostgator this morning (about a day later) which may be helpful to someone else who encounters a similar issue:
Thank you for contacting Hostgator support and I apologize for any delay in addressing your request. I see you are needing to restore a database on your account. In future we would suggest submitting restore requests through the form at https://www.hostgator.com/restore so that your request is sent to our restore queue which is prioritized and receives much quicker responses due to the nature of the work involved. We do have a backup of your databases, however we will need to know which database name you are wanting restored so that we can get this taken care of for you. Please note that restoring from our weekly courtesy backups incurs a $15 restore fee which we will require your approval for. I should also mention that the MySQL databases is the only thing we will have a backup of for your account, as your account exceeds the 20GB disk space limit for it to be included in our full home directories backups that are performed each week.
I probably would have paid that if they had come back to me sooner but it doesn’t seem to be useful given what we managed to achieve in the meantime.
Nathan, his coder and I couldn’t work out what the problem was with the WordPress XML file. I also noticed that my site on Hostgator was experiencing more downtime than usual (I use Monitor in JetPack and receive email notifications when my sites go down). I decided to explore a new host and a few people recommended Media Temple:
— Craig Jamieson (@craigjamieson_) October 27, 2014
I created an account with them to use their “Grid” shared hosting. They had a special going where I could get the first month for $5 instead of $20 so it wasn’t a difficult decision.
It took a little time for me to get used to the Media Temple control panel. I also discovered that my upload limits on Media Temple are lower than Hostgator (20MB versus 64MB) and I had to chat to support to increase a default 2MB upload limit to 20MB but once that was done, I got started on a new test site.
We hit another snag in phpMyAdmin on Media Temple because the file upload limit there is 10MB and my SQL download from the partially recovered site on Hostgator was 17.5MB. The next plan was to focus on the WordPress XML file to restore my site. I used a handy WXR splitter app which Nathan found and split my WordPress export into small chunks and started importing them into the site.
I discovered that when I imported the oldest posts into the new site on Media Temple, I still had that publication date issue so I deleted the test site and started again. I opened the XML file that was giving me a problem and noticed this weird date string in the post metadata of the old posts which weren’t being dated correctly:
This is from the pub date metadata of one of the entries: <pubDate>Wed, 30 Nov -0001 00:00:00 +0000</pubDate>
Notice the -0001 year in the string? Somewhere along the line the dates for those posts were changed to this -0001 year. That seemed to tell WordPress Importer to give those posts the current date and not the correct publication date. I located all the posts with that buggy year and deleted them from the XML file and imported it. That worked but I didn’t have all those posts in the new site.
The posts seemed to be from the early 2000s so I went back to an old XML backup which didn’t have that buggy year and imported that backup. That created some duplication in the posts so I found a plugin that de-duped my blog posts. I imported the pages from my portfolio site and found myself with a complete site (at least from the perspective that all my posts seemed to be in place).
I then exported the XML file for the reconstituted site, deleted the test site and changed my DNS settings for this domain to point to Media Temple’s servers. I had to break up my XML file again using that WXR splitter app and started importing the fragments into a new site which I set up on Media Temple’s servers. The domain registration changes took a little while to propagate and that interfered with the restoration a little but I finally uploaded all of the XML fragments and reconstituted this site.
I used the Velvet Blues url update plugin to change the internal links in this site to the new domain and I’m not checking whether all my attachments and images have made it across from my locally stored backup directory.
I still have work to do behind the scenes to complete the restoration but I have an almost complete site. I learned a couple things in the process:
- Make regular backups of your databases and your WordPress XML files
- Prefer hosting services that automate the backup process for you in case you forget or (like me) don’t even think about it
- Persistence is essential for this sort of thing, there is usually a fix, you just have to find it
- I know a lot more about WordPress, databases and XML files than I did a couple days ago
The hero in all of this is Nathan. I was just chatting to him about my site’s migration and he just started pitching in late Saturday night. I don’t know many people who work as hard as he does (I think he sleeps, I’m just not sure when) and he still steps in to help me when I hit a snag. As snags go, this was one of my most spectacular DIY (Destroy It Yourself) efforts and he was with me the whole way, messaging me with suggestions, feedback and uploading modified versions of my databases and XML files. If it wasn’t for Nathan, I may have given up on my site as lost. Instead, I have a site which seems to be complete and turns 10 in December (here is my first post). He is very modest but, holy cow, he helped me out of a spectacular mess.
@pauljacobson happy to help 🙂
— Nathan Jeffery (@nuclearpengy) October 27, 2014
Here is more information about Nathan:
If Nathan can help me with this in his spare time (granted, very limited spare time), imagine what he can do for your company when he is totally focused and has his team with him. In case you are wondering, this isn’t a paid or bartered plug. Nathan selflessly stepped in and helped me out in a big way. That should tell you a lot about him, his integrity and dedication and why you should talk to him when you need work done. I am enormously grateful.
Something else I was thinking about throughout this process is that all this effort we put into restoring this site merits much more attention to my blogging going forward. I’ve been a writer since I was at school and its always been something I love doing. I intend doing more of it more regularly, barring more DIY experiments …