Restoring Backup with phpMyAdmin

How did you make the backup? The file you are importing into phpMyAdmin must be a SQL file, with no php.

As for deleting the tables before importing, that depends on what is in the backup file. The tool I use for backup writes ‘DROP TABLE IF EXISTS’ commands in the SQL, so it does create the tables from scratch.

Here is a sample of the SQL from my backup:

#
# Delete any existing table <code>wp_comments</code>
#

DROP TABLE IF EXISTS <code>wp_comments</code>;

#
# Table structure of table <code>wp_comments</code>
#

CREATE TABLE <code>wp_comments</code> (
  <code>comment_ID</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  <code>comment_post_ID</code> bigint(20) unsigned NOT NULL DEFAULT '0',
  <code>comment_author</code> tinytext NOT NULL,
...
  PRIMARY KEY (<code>comment_ID</code>),
  KEY <code>comment_approved</code> (<code>comment_approved</code>),
  KEY <code>comment_post_ID</code> (<code>comment_post_ID</code>),
  KEY <code>comment_approved_date_gmt</code> (<code>comment_approved</code>,<code>comment_date_gmt</code>),
  KEY <code>comment_date_gmt</code> (<code>comment_date_gmt</code>)
) ENGINE=MyISAM AUTO_INCREMENT=210 DEFAULT CHARSET=utf8 ;

#
# Data contents of table <code>wp_comments</code>
#

INSERT INTO <code>wp_comments</code> VALUES (13, 300, 'Mike and ...
INSERT INTO <code>wp_comments</code> VALUES (14, 378, 'rupert' ...
INSERT INTO <code>wp_comments</code> VALUES (8, 300, 'Mac' ...
...

Thanks for your reply vtxyzzy.

I created the backup file with the wordpress plugin called: wp-db-backup. The backup file it creates is a zipped SQL file which I have unzipped.

This plugin also writes DROP TABLE IF EXISTS’ commands in the SQL and generates code similar to your sample that you posted.

Is there some other way to restore a back up?

I don’t know of another way to restore a backup. And, FWIW, restoring my backup only takes seconds for a SQL file of 4600+ lines.

It sounds to me like your php installation may be missing something. Can you check with your host? Maybe the php error logs might give a clue.

Hi – I hope that I understand your issue and that what I offer is of some use…

I recently used wp-db-backup to help me move five of my sites to a new host with no issue. I would add that I used the plugin’s default settings to back up my dbs, and that all were restored successfully to a new server.

First thing that comes to mind reading your post is that there may be a maximum file size for importing databases to your server. If that’s the case, and your db exceeds that maximum (usually ~10M), then it’s possible that this is the cause of the incomplete import. There is no notification on the server side if this is the case, although you may see that any number of tables appear to have been imported.

Also, when importing into a newly created database, any tables that exist in that database, including any that were created on earlier, failed import attempts, should be dropped before attempting to import anew. IOW, You want to import your tables into an empty database.

If your attempts at importing continue to fail, you can still import your database manually (cutting and pasting) using the instructions at the bottom of this page.

Good luck!

Thanks vtxyzzy. My SQL file was only 700 kb.

After much frustration I contacted my web host. I uploaded the SQL file to the server and asked him if he wouldn’t mind restoring it. It took a few seconds and it was imported successfully. I asked him if he had any idea why I was unable to import the SQL file from my end. His sugestion was that it was possibly a fault due to incompatibility issue with the browser I was using. That was something I didn’t even consider. I was attempting to do the import with my default browser: Safari.

I have not yet tested this by trying Firefox but it sounds feasible as I have experienced this same problem in the past with another host. Again, I was using Safari.

Thanks for your copy and paste tip breakingball. I was wondering if that was possible.

I just did this and found that since I’m allowed only one DB, I had to delete the old one first, recreate it, then upload the file, unzipped.

However, I still cannot find the hack. ??

What hack were you looking for?

Judging from your handle I am guessing that you are on a Mac. And so I would love to know if you used Safari with phpMyAdmin to import your unzipped SQL file?

Or did you have to do this with Firefox?