Fundamental to moving your WordPress blog is backing up your current blog’s database and restoring it on your new server, so I followed the backup and restore instructions with the utmost of care.
However on trying to restore the database on the new server I received the following error:
#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 ‘cat_ID` bigint(20) NOT NULL auto_increment
No amount of changing the syntax of the Create Table statement would get it work – hair was being pulled out in frustration!
I tried another tack. In phpMyAdmin, I clicked on the SQL tab and pasted the create table code directly in here from my saved .sql file (with no modifications) and it worked!
Having successfully created the table, I took the code from the .sql file for adding the data and ran that in the phpMyAdmin SQL tab and that worked as well.
I repeated this for all tables in my database and I soon had my database restored on the new server. Phew!