Moving A WordPress Blog To A New Host

I facilitate several WordPress blogs, several of which I installed on DiscountASP.NET.  I wrote a blog about the process, and then as now it felt like I had to shoehorn WordPress onto their system.  I, as well as others, began to have problems administrating our blogs (notably, difficulties adding plugins or upgrading versions).

I’ve also hosted a number of domains with Applied Innovations, and I knew they added some new capabilities and a crazy new plan for Webmatrix hosting, especially ensuring the right capabilities for WordPress and other WAMP stack applications (such as Joomla, Drupal, and so on).  Better pricing and the right features-it was time to migrate.

I migrated two blogs today using the steps below, and I’m going to migrate a couple others as their current hosting nears expiration.  Moving a WordPress blog involves moving both files and a database; each has its own method.

There are a number of ways to backup and restore a WordPress blog.  The official documentation is http://codex.wordpress.org/WordPress_Backups and http://codex.wordpress.org/Restoring_Your_Database_From_Backup.  There are several plugins you can install which will backup your blog on a schedule—it’s not a bad idea to use these.  I think the method below is pretty easy and effective for a migration, but isn’t necessarily a process you want to repeat every week as part of a regular backup (unless you script it, which is possible).

Step 0: Get The Tools

I used two tools to migrate my blogs—FileZilla, to transfer the files, and MySQL Workbench to migrate the databases.  Both are free and widely available.

Step 1: Prepare Your New Host

Before you do anything, make sure your new hosting is set up, that you can log into your control panel, and you have both a new website and database set up.  On some hosts (such as AppliedI), the FTP login is the root folder, but the WWW service points to s subfoler—make sure you know where to put the files.  Keep track of your passwords!

Step 2: Clean Up Any Messes

Migration will involve exporting data from your current blog’s database, then importing all of it again into your new blog’s database.  This process, on both ends, can take some time, so obviously the less data you try and move the easier it will become.  This is the time to log in to your control panel, empty all the spammy and trashed comments, clear out posts which haven’t been published if you don’t plan on finishing them, etc.  Also, if you have any custom DNS entries made on your current host’s nameservers, such as redirecting email to GMail or Hotmail, copy those settings.

Step 3: Download The Blog Files

Log in to your current blog, and download all the files.  You’ll need everything in your blog root, as well as the three subfolders (wp-admin, wp-content and wp-includes).  Download these to your local machine, because you’ll need to edit one of them.  Don’t delete anything from your current site, just in case you need something again.

Step 4: Export Your Blog’s Data

This step requires the most explanation.  It’s not difficult, there are just a number of small things which need to be done.

Start up MySQL Workbench; the Workspace area is divided into three columns, the far right of which is Server Administration.  Below this listbox, click New Server Instance.

image

Choose the Remote Host option, and enter the name of your current blog’s MySQL Server, then click Next.

image

Give this connection a better name in the Connection Name box; this will populate a connection in the SQL Development column.  Change the Username if necessary (it probably is).  If you want/can store the password in MySQL Workbench, click the Store in Vault button next to Password, and enter the password in the dialog.  If you don’t store the password, you’ll be prompted when you try to connect.  If you need to, adjust other settings, but you probably won’t need to.  Click Next.

image

The database connection settings will be tested.  If Workbench cannot connect, you’ll need to fix whatever is wrong before you can continue.  Click Next once everything is OK.

image

Remote management takes some additional configuration, and it’s not something we’re going to do here.  If you want to use MqSQL Workbench to manage your database, you can set this up, but it’s not necessary to export/import data.

image

A bunch of intermediate steps will be skipped, and we then assign a useful name to the new Instance.  Clicking Finish here will close the wizard and create the connection and the instance.

image

Select the new instance with a single click, then choose to Manage Import/Export.

image

You may be prompted to choose the connection.

image

This is the most important set of our export.  Select your database with a single click, then choose which items correspond to your blog.  In a WordPress database, there are only tables, and by default they are prefaced with “wp_”.  The image below shows a default install, for reference.  To make the import easier, choose to Export to a Self-Contained file.  For most blogs, this will be OK, but for very large blogs, you can choose to export individual tables into separate files.

image

The export will take a few minutes—watch the progress indicator.  When complete, you can close the Import/Export window.  We need to make a couple small edits to the export file before we can use it.  If you exported to multiple files, you’ll need to make this change to all of them.

About 22 lines down is the command to create a database if it doesn’t exist.  Since our user probably doesn’t have the correct permissions to do this, we need to commend this out by adding two dashes and a space at the beginning of the line (the space is necessary).  A couple lines further down is the USE directive.  This needs to be changed to the name of our new blog database.

image

image

Step 5: Import Your Blog’s Data

If you haven’t done so already, make sure your new blog database is set up, and has a login user ID created.

Back in MySQL Workbench, under the SQL Development column, click the New Connection link.

image

Just as before, give this connection a useful name, then update the Hostname and Username (if necessary).  Again, if you want/can store your password in the vault, do so.  Once everything is correct, click OK.

image

When all is well, we’ll have a new entry in the Connections listbox.  Double-click the new blog entry to open the SQL Editor.

image

In the SQL Editor, open the dump file you created above, double-check you made the minor (but important) edit above, and run the script.  The tables should be created and populated.  If you dumped to multiple files, you’ll need to repeat this process for every file.  When all the scripts have been run, you can expand Schemas >> [your database] >> Tables and see the WordPress tables.  If you’re familiar with SQL, you can query some data at this point, too.

image

Step 6: Upload Blog Files (edit the config first!)

Before we do anything, we need to modify the database connections in our downloaded wp-config.php to point to our new host’s database and server.  These are the first four settings in the file.

image

Save this file, and upload all the files to the new host.  Make sure you’re in the right folder, whether it’s the root or a subfolder.  Once this is complete, you should be able to view your blog through a temporary URL, or your actual URL, depending on how your site is configured at this time.

Step 7: Update Nameservers

Since you’ve moved to a new host, it’s now time to update the nameservers for your domain.  This is now a good time to download any email you want to keep from your host’s email system.  You’ll probably need to update your email clients, too.

Congrats!  You should soon have your blog completely set up on your new hosting service.  Remember to cancel your old service!

Another Solution To “The list cannot be displayed in Datasheet view”

I have a fair amount of information in SharePoint 2010 (just as I did in MOSS 2007), and for speedy editing of a lot of the entries, there is no beating the datasheet view. For some time now I have been getting the following error when I tried to edit the list in datasheet view:

The list cannot be displayed in Datasheet view for one or more of the following reasons: A datasheet component compatible with Windows SharePoint Services is not installed, your browser does not support ActiveX controls, or support for ActiveX controls is disabled.

There are a lot of answers on the interwebz, none of which solved the problem for me in the past.

Recently, though, I found an answer accidentally. I was trying to import some data from an Excel spreadsheet into a SQL Server database using the Import Data task. It was throwing an error that I was able to track down to having Windows 7 64-bit. My Office 2010 is 64-bit, and the import data wizards were 64-bit, also. However, Access and Excel importers are 32-bit only, and unless you knew to select 32-bit wizards also during installation, you don’t have them on a 64-bit machine.

Finally I found a form post that pointed me to the Office 2007 Data Connectivity Components (formerly known as the Access Database Engine). Installing these solved my data import, but also fixed my datasheet view!

Now that I can load the datasheet view, this makes a lot more sense. The Access icon is clearly in the upper left corner, and the status bar says “For assistance with Access Web Datasheet, see Help.”

So, if you’re getting the same error in SharePoint, and you’re running the 64-bit version of Office, and nothing else has worked, try the Data Connectivity Components from http://www.microsoft.com/download/en/details.aspx?id=23734.

A SQL Script to generate a 4-4-5 Mon-Sun calendar table

In a previous life, I did A LOT of BI reporting, and everything we did was based on the company’s 4-4-5 fiscal calendar, and a Mon-Sun work week.  Adding to the fun was the mutant Julian style date JD Edwards uses internally.  This system violated every known calendaring convention, but “that’s how it’s set up in JDE”, and if you’re a JD Edwards shop, you know that argument renders all others invalid.  Oh yeah, the fiscal year started on a different day every year.

To make our lives easier and our apps and reports more performant, we utilized calendar tables.  Problem is, every couple years, we had to add more dates to the tables.  When this duty fell onto me, rather than being The Last Person, and spend all day fiddling with Excel and a subsequent import, I spent part of a day writing a simple script that can be run whenever necessary to add more dates.

Because we were integrating information from a number of systems (WMS, TMS, LMS and payroll, notably), all with their own equally mutant calendaring systems, we have a fairly wide table with all kinds of date indicators in it.  This script is kind of a recursion hell, but it needed to be in order to iterate all of the output columns correctly.

Below are the comments from the SQL file; you can download the entire script from this link: 4-4-5 Calendar

/*

This script is meant for a 4-4-5 calendar, Mon-Sun week. Every leap year introduces an extra week, which we add in November.

User Variables

FiscalCalendarStart = The date on which a fiscal year starts. This is used as the base date for all calculations

EndOfCalendar = The date on which the calendar should end. This does not have to be the end of a fiscal year, but if it’s not, you might have to run the script again to get to the end of the fiscal year.

RunningDaySeed = Usually 1, this is used to measure the number of days since the calendar began, often used for depreciation

RunningPeriodSeed = Usually 1, the number of fiscal months since the original calendar began

RunningWeekSeed = The number of fiscal weeks since the original calendar began

FiscalYearSeed = The starting fiscal year

Iteration Variables–don’t mess with these

JdeJulian = the date expressed in JDE’s Julian format

CurrentDate = The calendar date being calculated against

WorkWeekSeed = Fiscal Week

WorkPeriodSeed = Fiscal Month

WorkQuarterSeed = Fiscal Quarter

WeekOfMonth = Rolling week of month

FiscalWeekEnding = Last day of the fiscal week

WorkPeriodSeed = Some legacy thing we must have or the world will end.

But, used to assign where the extra “leap week” goes. Based on the 4-4-5 calendar.

IsLeapYear = 29 days in February extra week in November?

 

Output Columns (most of these exist to make reports easier)

DateSID = JDE’s Julian Date

CalendarDate = the date, duh

Sysdate = YYYYMMDD, based on calendar date

RunningDay = the number of days since we installed JDE

WorkPeriod = roughly correlates to the fiscal month

RunningPeriod = the number of fiscal months since we installed JDE

WorkWeek = The fiscal (Mon-Sun) week of the year

RunningWeek = the number of fiscal weeks since we installed JDE

FiscalYear = The numeric fiscal year

FiscalYearLabel = the pretty field used on a report

WorkQuarter = The fiscal quarter

FiscalQuarter = Another representation of the fiscal quaruer

FiscalQuarterLabel = used on reports

FiscalPeriod = YYYY + WorkQuarter + WorkPeriod (zero-padded)

FiscalPeriodLabel = used on reports

FiscalWeek = YYYY + WorkQuarter + WorkPeriod (zero-padded) + week of the fiscal month

FiscalWeekLabel = used on reports

CalendarYear = calendar year, duh

CalendarQuarter = the traditional definition of a quarter

CalendarQuarterLabel = used on reports

CalendarMonths = the traditional calendar month

CalendarMonthLabel = used on some reports

WeekEnding = the last day of the calendar week (Saturday)

FiscalWeekEnding = the last day of the fiscal week (Sunday)

FiscalMonth = Based on the fiscal calendar, relates to the WorkPeriod somehow

FiscalMonthLabel = used on some reports, based on the FiscalMonth

*/