Restoring from an incomplete database dump

[valdet]

Hi there,

I am trying to help a friend of mine in restoring a database from an incomplete database SQL dump file.
Yesterday, he had the worst nightmare of website owners: Server crash.

Unfortunately for him the hosting company backup server had also crashed and the latest available database and file backup that hosting company had for him is two weeks old backup.

Few days ago he did a backup of vBulletin database from vB Admin Panel, but it turned out that it was not completed. The last table to be downloaded was vboard_user table.

I want to help him restoring as much content as possible, but the most important are tables: post, thread and user.

Now i have both database dumps available. original.sql (2 weeks old) and backup.sql (more recent - 3 days old).

I want to see if someone has an experience or could advise me on which best options are available to merge the contents of these two dumps or update original.sql with fresh content from backup.sql

In my head I am thinking of taking the backup.sql and doing a mass replace of data from

INSERT INTO `vboard_xxxx`to

REPLACE INTO `vboard_xxxx`and import it into database. In that way I presume all contents of backup.sql will be added to current original.sql.

Or I may go the otehr way around of creating a completely new database on phpMyAdmin and copying the contents from both of these dumps into the new database.

I found some resources, but I am not sure if these would work in my case, because both files have many tables with same records (older threads, older posts, older users etc..)

http://www.ozzu.com/programming-forum/merge-two-databases-phpmyadmin-t49395.html
http://www.daniweb.com/forums/thread145892.html



Can you please give me any clues how would you best solve this difficult thing.

I would hate if I have to go one by one on my backup file and isenrting the threads and posts manually through sql statements.


Thanks and I appreciate any comments you might have.

Regards,
Val.

[carcomp]

It might be a better idea to try a restore using bash. You can contact your server company and ask if you can run the following code...

mysql -u user_name -p your_password database_name < file_name.sql

where user_name and password are the logon for your db (same as vbulletin uses in config.php) and database_name is your database name (same as vbulletin uses in config.php). Your file_name.sql would be backup.sql

That command will overwrite any current data in the database so watch out. However, it *will* be a full restore from your file. I've found that some "web" restore things like vbulletin and others in php will chop off the end of your file, fooling you into thinking you have a bad .sql file.

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name file_name.sql

also in the future, to do a REAL backup of your database, bash this.

mysqldump -u user_name -p your_password database_name > file_name.sql
(this will store to your ftp login default dir) at least it should if you are set up right.