Restoring partially a database

[RMerce]

My hosting company made some upgrades to their servers and in the process the character set of the data in my shop database got corrupt (just some special characters for the portuguese language weren't showing correctly). I've managed to restore the whole database to an earlier backup with the correct character info, but that backup is rather outdated. Since it was saved, there were more customer registrations and orders, which were stored in the corrupt charset database.

 

So, what I'd like to do is append to the older database just the registrations and orders made after the server upgrades were done. Somehow, the data added to the database after the upgrades aren't corrupt, just the older data (which I restored with the backup).

 

What I don't know is which tables I should work with. What are all the tables that the customer data are saved into? From browsing the "customers" table, I've found that it isn't the only one, as not all data is stored there (from what I understood, the adresses are stored in another table, the orders the customers made in yet another one and so on)... My plan is to locate these tables and then select only the data that was saved after the upgrades for on the next step append to the restored database. I still have a lot to learn about MySQL, so every help I can get on this particular "task" will be very much apreciated!

[♥geoffreywalton]

To find out which tables relate to customers you need to lok at the mysql database.

 

1) Go to your ISPs admin area for your site.

 

2) Click on the icon for Database administration or Mysql, then find the link for phpmyadmin, it might be hidden away at the bottom but not on all admin sites. If you can’t find this, please consult your ISP's help desk.

 

3) In some cases, you may have to choose which database your OSC tables are located on. If this is the case, please choose using the dropdown box.

 

4) Click on "SQL" on the navigation bar.

 

5) Enter/paste your SQL in the textbox provided or, if you have a file containing the sql commands, click on Import and follow the on screen prompts.

 

6) For both methods click "Go"..

 

7) If all goes well, your SQL query will run successfully

 

For another explanation see:-

 

http://www.oscommerce.com/forums/index.php?sho...dmin+mysql+code

 

Or for a contribution to allow you to run SQL Script from Admin

 

http://www.oscommerce.com/community/contributions,4567

 

On a stock osc installation you could probably get away with customer info and address book. But what about order history and do you have extra contributions such as customer extra fields?

 

If you have 2 backupfiles, one of the old db and one from the new db you can make a new combined backup file using a text editor and cut and paste.

 

Make sure you keep a copy of the originals of both of these files.

 

I suspect your current backup has a different collation set in the mysql for the db and/or the table. Probably went from utf8 to swedish. Normally in the backup file, for each table, there is a drop table command followed by the commands to create the table and field, then the actual data to go in the table

 

You can just delete all the records for the tables which are OK and just work with a smaller file.

 

If you have a backup of the current file you can always get back yo the current position.

[RMerce]

Thank you!! :thumbsup:

 

I just finished adding the data (via phpMySQLadmin) and it seems to have worked great. The tables I appended the data to were:

- address_book

- customers

- customers_info

- orders

- orders_products

- orders_status_history

- orders_total

 

Although I have made many mods (some by myself and other through contributions) to my store, very little of them have changed the DB, except some contributions for stock status and payment modules, but none of them changed the actual order tables. I hope it works out all right! :)