Data Recovery

I was clever enough to delete part of the data from a table in a database. It was over 2500 product images used for an eshops we operate. Historicaly it was the oldest images put into the database. It took few days to discover the disaster. And since then new data was entered into that particular table.

Luckily we found an older backup. However we couldn't just restore data from the backup to the database. We would lost new data. And I didn't want to get into comparing ids in the old and new database. It took me a while, but I found a soution.

First I created temporary database on a production server. I then loaded old backup into it. I found out that the old table structure differs in a detail from new one. Then, after few tries on a test database, I loaded data into production db, like this:


INSERT IGNORE INTO eshops_prod.product_images
SELECT i1.*, 0
FROM temp.product_images i1
LEFT JOIN eshops_prod.product_images i2 ON (i1.image_id=i2.image_id)
WHERE i2.image_id IS NULL


This quick trick saved me now....