Magento Tutorial – How to Restore Magento Database Backup
May 31, 2012, by admin
The Magento database creates use of foreign key constraints to make sure database reliability. As an example, if a category is deleted, all categories below it in the category tree must be deleted too.
If you make a backup of your Magento database using the built-in backup function (Admin > System > Tools > Backup), Magento inserts special statements in the .sql file to avoid foreign key checks when the tables are restored.
If you backup your Magento database using other tools, like phpMyAdmin or Navicat, these special statements will be missing. When you attempt to run the .sql file, you will get errors like these:
Cannot add or update a child row: a foreign key constraint fails
This error arises because the data you are importing is offered table by table, row by row, without regard to the logical structure and reliability of the database.
To restore a .sql file backup without limitation checking, simply add the following statements at the beginning of your .sql file:
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’;
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
At the end of the file, add the statements needed to turn on restraint checking again:
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;
With these modifications, you should be able to restore your database from a .sql file backup created with any tool.
If you have similar problems installing the sample database, just add the same statements to the .sql file containing the sample data.
