Magento Forum

Restore backup for a newbie
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

I upgraded Magento but saved a backup copy of my data, but I have no idea how to restore the data so the latest version can see the work I’ve already. I’ve been wading through the forums and I cannot find a simple, “here’s how to restore a backup” kind of guide. can anyone help a newbie/dummy restore their data? thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
JoeNmass
Member
 
Avatar
Total Posts:  50
Joined:  2008-03-07
 

I tried to update to the latest version of Magento but had a file error, it may have been from a bad installation on my first Magento install. I ended up doing a complete clean install after I couldn’t updat the new database. Maybe you will have better luck. Here is how to update your new database with the old information.
Go to the administration section of Magento then click on System-Tools- Backup.
Back up the data to a .sql file. From log into your new database by going to Mysql admin section. Once there you will find a “upload file” button to click on. Browse to the backup .sql file you downloaded from Magento then click on the upload button. Your database will be updated with all the information you had saved.

Give it a whirl. Hope it works for you.

 
Magento Community Magento Community
Magento Community
Magento Community
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

Thanks Joe.

When I try to import the backup file, however, I receive the following messages:

“Error

SQL query:

DROP TABLE IF EXISTS ‘admin_assert’;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’admin_assert’’ at line 1”

what should i do now?

 
Magento Community Magento Community
Magento Community
Magento Community
 
jan212
Guru
 
Avatar
Total Posts:  407
Joined:  2008-01-03
 

do you have shell access?

 
Magento Community Magento Community
Magento Community
Magento Community
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

unfortunately, being something of a newbie, i really have no idea. i’m trying to use this on a media temple grid server, which i was able to set up using the 1-click install process. any idea where i can find out if i have shell access? if i do, then what do i do?

 
Magento Community Magento Community
Magento Community
Magento Community
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

ok i figured out shell access and how to potentially restore the data, but i get the following error when i attempt:

“ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’admin_assert’’ at line 1”

i have no idea what to do next. please help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

can anyone help me with this please? thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Yoke Lee
Sr. Member
 
Avatar
Total Posts:  80
Joined:  2008-05-08
China - Singapore
 

can u tell me what environment you’re working ?
i mean the PHP server. do you use linux or windows ? do you use XAMPP ?

you can always go to phpmyadmin if you use xampp or phptriad or such…
http://localhost/phpmyadmin/

choose your database and export structure and data of all tables as file something.sql
there are such options on the right. you can tick it. maybe you shouldn’t tick the drop table…

if you don’t mind, maybe you can send me your sql file, i’ll help you analyze. it should be nothing big.

 
Magento Community Magento Community
Magento Community
Magento Community
 
grooves1200
Sr. Member
 
Total Posts:  122
Joined:  2008-02-01
 

i did use phpmyadmin to upload the backup file, which is an SQL file that magento itself created. i had the issue with the quotation marks that someone else documented in another thread, but corrected them and the file was imported successfully. but now when i try to access magento, either on the front end or the back end, i’m not seeing anything other than 403 and 404 errors.

 
Magento Community Magento Community
Magento Community
Magento Community
 
uni-man
Member
 
Total Posts:  34
Joined:  2008-04-04
 

Hi

When trying to restore from a backup made via the magento admin, i get the following message (in phpmyadmin)

INSERT INTO `core_store`
VALUES (
‘0’, ‘admin’, ‘0’, ‘0’, ‘Admin’, ‘0’, ‘1’
), (
‘1’, ‘default’, ‘1’, ‘1’, ‘Naturally Organic’, ‘0’, ‘1’
);

MySQL said: Documentation
#1062 - Duplicate entry ‘1’ for key 1

but I don’t see why this should happen: key 1 is a unique index but the requited fields are actually unique and the records are created successfully…

(Magento v1.0.19870.1, MySQL 5.0.45)

My only option left is a fresh install and reconfigure but I’d like to avoid this after having manually types in 200 manufacturers and a bunch of complex attributes… because there is no way to export / import attributes yet…

 
Magento Community Magento Community
Magento Community
Magento Community
 
uni-man
Member
 
Total Posts:  34
Joined:  2008-04-04
 

A quick workaround:
export your database using phpmyadmin (NOT the magento admin built in one)

Make the edits described here
http://www.magentocommerce.com/wiki/restoring_a_backup_of_a_magento_database

it now works…

 
Magento Community Magento Community
Magento Community
Magento Community
 
MageParts
Guru
 
Avatar
Total Posts:  415
Joined:  2007-11-18
 

Yo, I’m trying to restore my backups from a module extension in Magento, works fine except that the attributes are not being restored. Will I’ve to figure out how to export the attribute tree or is there a work around? Note that I can’t use phpMyAdmin, NaviCat or any similar programs, it needs to be done internally. Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
mattbill
Jr. Member
 
Total Posts:  3
Joined:  2011-01-28
 

I was recently searching for how to restore from Magento’s backup tool (Admin > System > Tools > Backups), but couldn’t find the answer in the forum. Here’s the solution that worked for me. (Caveat: I don’t accept any responsibility whatsoever for your site. If you don’t feel comfortable doing the following and screw up your site, don’t blame me.)

First of all, any time you use Magento to make a backup, it creates a .gz backup file (a type of compressed file) in /var/backups/ . If you’re like me, maybe it took a while to find out where it was storing them.

Secondly, you have two options for restoring your backup. You don’t need to empty out your current database before doing either of these as they will overwrite everything.

Option 1) This option is much easier, but will only work if your database is relatively small (like 2MB or less).
- Use an FTP program to download the .gz back up from /var/backups/ . If you have more than one backup, you’ll have to look at the create dates and guess which one is the one you want.
- Login to phpMyAdmin
- Navigate to store’s database
- Click the Import tab
- Click the Browse button, find your .gz file on your computer, and hit Go.
- Note: phpMyAdmin has limits on the size of the database you can upload, so your database must be pretty small (like 2MB or less). If you have 50,000+ items and a 50MB database like I had, you’re going to have to look at option 2. (Alternatively, I’ve heard you can get fancy with changing limits in .ini files, but I didn’t go that route.)
- Now, looking at old posts from 2009 and before, I’ve seen people say that you need to add several lines of code at the beginning of your database file to preserve foreign key constraints. I’m not sure if this is still true, but I’ve backed up and restored my database a couple different ways and haven’t run into this problem.
- And that’s it. Navigate to your site, and hopefully, it should have worked - at least it did for me.

Option 2) If you have a large database and shell access (SSH), here’s another option. I had to do this because phpMyAdmin was imposing a 2MB limit on me. (Note: you probably do not have access to SSH if you only have cheap hosting.)
- Login to your SSH program
- Navigate to the var/backups folder in your Magento installation. (If you don’t know how to do this, you really need to learn SSH and maybe find a SSH commands cheat sheet.)
- Execute the following line. Use it exactly as I’ve written here, except substitute each [stuff] (including the [ and ] ) with your own info.
gunzip < [yourBackUpDB].gz| mysql -u[username] -p[password] [mySQLDataBase]
- If you can’t get that to work, contact your hosting provider. They provided me with this code and it worked perfectly for me.

Both of these worked for me. Also, I was able to use these solutions to duplicate a database, say for example, from a development to a production environment. (Although this is a completely separate topic that requires changing some values in your local.xml file and also 2 fields in your database.)

If you want more info on backing up Magento, here’s a good blog post I found:
http://screencastworld.com/2010/03/magento/9-methods-for-backing-up-your-magento-website-and-database

Best of luck. I hope this was helpful.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Golden Noe
Member
 
Total Posts:  36
Joined:  2011-02-25
 

\"mattbill\" YOU ARE GREAT!

It was so easy to restore a backup (magento 1.4.2) and i nowhere found a way to did that!

Thank you! (respect !!!)

 
Magento Community Magento Community
Magento Community
Magento Community
 
SleepingGiant
Jr. Member
 
Avatar
Total Posts:  20
Joined:  2011-09-18
Netherlands
 

@mattbill Thanks for your post, you rock!

 
Magento Community Magento Community
Magento Community
Magento Community
 
johnchamley
Jr. Member
 
Total Posts:  4
Joined:  2011-08-09
 

Wamp - MySql console - error 1064 (42000) - backup magento .sql.gz - restore database - localhost Windows XP

I just installed my first working local Magento store for testing on Windows XP using WAMP to install Apache PHP and MySql after struggling with XAMPP. Fixing the Windows XP issues with the localhost URL is not too bad if you read a few Forums and installing Magento as the IP 1270.0.1.

I had huge problems figuring out what to do with the database backup file i created in our live Magento web shop (magento - admin - system - tools - backup). Matbills post here is great clarity but i still missed one piece of the puzzle after i downloaded it using FTP from the webserver (www\\var\\backups)

My DB is only 400 SKUs but came to about 10mb zipped (sql.gz) and so like Matbills says \"is too big for PHPmyAdmin to import\” I even tried changing the PHP.ini file to increase the max file parameter for phpmyadmin.........

My next issue was trying to use \"MySQL Console\” which is a bit like a black DOS screen you see in windows if you run CMD - scary command line stuff. Many posts mention extracting the sql.gz file using a command like this but i had no success:
gunzip -c /path/backup.Sql.Gz | mysql -h[database host - usually localhost] -u[username] -p[password] [database-name]

After lots of Error 1046 replies from mysql console i read about extracting the file so i downloaded \"7-zip\" and extracted the SQL file.
i then tried to import the SQL file like this :
mysql -p -u[user] [database] < db_backup.sql.
More Error 1046

I had a sleep then read about :
*making sure the backup.sql file was in the right folder
*SSH needs small filenames like DOS
*make sure mysql console know which database you are working with

so i moved the backup.sql file to the same place Magento creates them (C:\\wamp\\www\\var\\backups) and renamed the backup file \"bodb1\" then used a very simple mysql console command i found people taliking about in database restore forums - SOURCE precede by the USE command :

use [the name of your database]; (a bit like navigating to the correct folder before pasting a file)
source ./var/backups//bodb1.sql; (notice the \";\" after sql)

This command opened the SQL file and copied all the tables to the local Magento database i had setup (with the same name, username and pwd as the live Magento database from which the backup was extracted)

hope this helps someone.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top