Easy WordPress Database Backup
You work hard to create amazing content. You also work hard revising and updating your site with new plugins, themes, and more. If it hasn’t happened to you yet, it will: a new plugin that was not designed to work with your version of WordPress breaks everything. You can’t get into the Dashboard and deleting the files via FTP doesn’t work either.
What do you do??? Luckily, you have a recent database backup. Here is a step by step guide for creating a database backup and restoring your site. Please note: you must have the backup file BEFORE you can restore your site.
Using phpMySQL or a similar SQL database interface to create a backup file.
a) Select “Databases
b) Select the database of your WordPress install
c) Click the Export tab
d) Choose all tables (or just those that apply to your WordPress install)
e) Make sure the following options are ticked: “Structure, Add DROP TABLE / VIEW / PROCEDURE / FUNCTION, Add IF NOT EXISTS, Add AUTO_INCREMENT, Enclose table and field names with backquotes”
f) Leave all other options as default.
g) Click “Go” which will generate a downloadable file.
PhpMySQL cannot handle very large files, so if the process doesn’t complete successfully, you may need to use direct SQL commands to restore. If that’s the case, check out the official WordPress documentation on the subject.
In order to restore a database backup, follow these steps:
a) In the same (or a new) MySQL database in which you would like the new database to be located, click the “Import” tab.
b) Browse to the file generate in your last backup and click “Go”. This may take a while, but eventually you should see a success screen.
Check out the official WordPress documentation on the subject.
Bonus: How to export all WordPress posts easily
If you need to move your WordPress from one installation to another, here is one super-easy way to transfer all content between sites.
a) In your WordPress dashboard, on the left under the TOOLS menu, click “Export”.
b) You will be select which authors’ posts to export and whether or not to include all resources (images) attached to each post.
c) Click “Ok” and you will download an XML file which can be uploaded through the Export area of any 2.x + release of WordPress.
d) That’s it! Easy huh?
Trackback from your site.
Comments (7)
I use plugin which takes scheduled backup for me and sends the file to my email id. I have a question, if I want to move my site to another host, I can use my db and make the blog running but what happens to the picture attachments in posts? Does the db or export carry the images with them? I think I need to copy the images/media files folder if I want to move my blog, right?
Hi Ria,
The db will include URLs to all images attached to posts. Providing all those directories are still intact, all images should still show up after a database dump.
Regards,
Cyrus
Having backup files of all your work on your site is crucial. It would be horrid to lose all the hard work you have put into your site over something silly.
phpMyAdmin is a great tool. I’ve used it for years. You’ve put a nice reference together here. I do like the idea of a plugin, but I haven’t tried it. Maybe I should think about a cron job to do it.
Curus, When there are tons of plugin available for easy wordpress backup. Is it good idea to take backup manually? (I used one that send me backup everyday by email)
Joel, you’re right. There are many great plugin options to handle backing up. This is just an overview of manual backup options.
Thanks for your comment!
Great tutorial Cyrus.
SqlYog is another fantastic tool to manage MySql database. It can be installed on any windows machine and it will let you connect to your database remotely. You will be able to do anything you want like database backup and restore. You will also be able to synchronize your database from one host to another. It is also an excellent tool to handle large database tables.