Topic: Restore Database
I have moved servers and I need to restore my database, I used the back up plugin so how do I restore it now?
Re: Restore Database
this can be locked I got it done 
3 2006-10-18 13:35 (edited by Mad-Wolfie 2006-10-18 13:36)
Re: Restore Database
I've a similar problem at present.
I've just had to move my site from one host to another host & encountered difficulty.
I have tried to run the SQL on the server control panel to upload the old database but there is a 5mb upload limit on the hosting & the database is 7mb.
So i am asking is it possible i can shrink the database size down.
I do have a local copy of MySQL on my PC which is used to run a database on a software package i have installed, so is there any way i could perhaps edit the site database in the local SQL package to get rid of some dead wood - I'm sure it's possible but i don't know how as I know about as much about this version of SQL as i know about hang-gliding - which is very little!.
I know there are a few tables in there I could get rid of, left over from an old OScommerce install that i got rid of, an old directoryinstall attempt that failed & some old punbb tables from past installations on a different prefix, left over when converting the database & installing a new updated forum script which would probably help me lose a couple of MB if i could shift them from the database.
Any help would be appreciated
Re: Restore Database
You could remove the inserts for the search_matches and search_words tables. That should shrink it a bit: you can rebuild the index after the import.
Alternately, I can give you the code to modify the database administration plugin to restore from local files. Then you can just upload via FTP.
Re: Restore Database
at the moment i'm uploading the SQL via a client on my hosting control.. the only other way i know of is to use an upload restore facility but i tried this & it still won't go over the 5mb threshold.
it's a case of i know what to remove, just i can't find a way to remove it as the SQL database file is just one big lump that i can't break down to delete the tables I don't need. Good call though on the search tables, something i'd not thought of... no doubt i'd have to rebuild the index anyway so i suppose it makes sence to remove the original search tables as well to free up a few more valuable bits of unneccesary data.
- elbekko
- Senior Member
- Offline
Re: Restore Database
If you have shell access to your server, do this:
$ mysql -u <username> -p
Password: <enter your password here>
mysql> CREATE DATABASE <db_name>;
mysql> use <db_name>;
mysql> \. <location of file to import .sql file>
mysql> \qThis will allow you to easily import everything alot faster than through phpMyAdmin, but I'm not quite sure if it works with remote locations (if not, just upload it to your FTP and find the absolute path to it).
Did it plenty of times for a DB I have to migrate alot. It's tons faster than phpMyAdmin IMO.
Re: Restore Database
Thanks Elbekko
i've tried the shell access approach but so far no joy... not sure if i have this or not. I'm still getting used to this new hosting package so i'm probably the cause of the errors when i try.
I've discovered that Araneae (which if you are not familiar with it is a text type HTML editor - an ideal tool for editing php files for doing forum & code mods.. by the way it's freeware!) can open the SQL file. I've opened the file & removed the search tables (which shrunk the file down to 2.4MB - which is the result I was after & all the old OScommerce tables as well as the bosdev directory files, however now when i run the SQL upload i get an error once the upload has finished "Invalid default value for 'id'. with the table information shown. At 1st i noticed the error with the old OScommerce tables left in & then later the bosdev directory files so i removed then but whichever table it comes to it seems to chuck out the same error. Any ideas?
Re: Restore Database
Here's a "free" nifty tool that I've used many times (which works great) for importing hugh database files for a work around for hosting providers that sets (MB) limits on sql database uploads:
Website Link: http://www.ozerov.de/bigdump.php
Download Link: http://www.ozerov.de/bigdump.zip
Hope this helps!