SQL error trying to restore WordPress backup

I moved my blog to this domain last weekend. I researched the best ways to move a blog and found some good clear articles on moving a WordPress blog written by Podz.

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!

2 thoughts on “SQL error trying to restore WordPress backup”

  1. I really should add that ‘paste the sql’ snippet to the page .. but there are quite a few other tricks that I could add as well, and I don’t want to risk people breaking their blogs. Unfortunately, it’s not a 1-2-3 type thing.
    Database issues seem to be #1 that I’m contacted for so far – and I’ve not lost one yet 🙂

    Glad you are up and running !

  2. I really should add that ‘paste the sql’ snippet to the page

    Do it!

    Seriously, put a section at the bottom, if you received the following error on restoring your db “#1064 – You have an error in your SQL….” click here with instructions on the pasting trick.

    I’m happy to help with this if you want – screenshots, whatever.


Comments are closed.