Miles of files

Automate WordPress Database Backups for Care-Free Protection

| 10 Comments

I’m sure you’re sick of hearing everyone reminding you to backup your database, usually before performing any upgrades to your site. Well, this is largely because irreparable damage can be done if something goes wrong with your database and far too many people rarely, if ever, backup their database.

I used to be in the same boat, but I’d be lying if I said I didn’t regret it on more than one occasion. As your sites and their content and comments become invaluable and irreplaceable, you’ll see the value in backing up your database, where all the “meat” of your site is contained including post content, settings, categories, links and taxonomies.

There are many ways of doing this, but for most people, the following method I’m about to show you will more than suffice and you’ll never find yourself with lost content.

Get a GMail account

First off, you’ll need to get a new GMail account (or your personal one if you frequently have loads of free space left). You can use any email account really, but I recommend GMail because the free space provided is huge and you’ll need the ability to filter email like GMail can.

Install WP-DB-Backup

Now you need to install the plugin that is going to perform the backup for you without your input. Install the WP-DB-Backup. You can either search for and install it from within your WordPress console, or download it directly from WordPress.

Set up WP-DB-Backup

WP-DB-Backup Settings

Tools menu

Once the plugin is installed and operational, head over to the settings page from your admin console, by going to Tools->Backup.

Once in the settings panel, you’re going to set it up so that the plugin performs a SQL dump (export of all your database data) on a regular basis and emails the resulting .sql file to the email address we set up above.

The page is split into three sections – ignore the first two for this tutorial – we’re interested in the scheduled backup section.
WP-DB-Backup Settings
Make sure that you select all the tables listed (unless you’re concerned with file size and are certain that you wouldn’t miss the data if it disappeared). Then you’ll need to set up a frequency for performing the backup: this will largely be a function of how often your blog changes and how large your SQL database is (and thus how much storage you will need to keep an archive of SQL databases in your email account). My SQL backups tend to be about 700KB, so a 30-day archive, based on once-a-day backups will only use about 21MB of storage space (compared with GMail’s 7GB+ of storage).

For most people, once a day is more than adequate. Unless you’re running a high activity blog, you’re unlikely to need anything more. Bear in mind that performing this function weighs down your server periodically, so if you’re doing a large backup every hour, you might notice a degradation of service.

Now that you’ve selected your backup frequency, enter your email address that we set up earlier and hit Schedule Backup.

Set up GMail filter

Creating your GMail filter

Creating your GMail filter

I set up my GMail account so that it will keep 30 days worth of backups and then automatically delete them. I figure that if there’s a problem with my website, I’ll figure it out within 30 days! Again, this is done completely automatically (after setting up this filter) so that once you set it up, you never need to do anything again. Of course, if you want to keep more than 30 days of backups, that’s simple to do as well.

Setting your filter settings

Setting your filter settings

Login to your GMail account and head over to the Settings page, and then head over to the filters tab. Now you’re going to “Create A New Filter”. Now insert the information as shown in the images to the right. Bear in mind that you will need to change the From address to reflect your own domain (wordpress@yourdomain.com).

Now before you start freaking out once you notice that I’ve checked the “Delete It” box, listen up. What this filter does is it takes all of your backup emails, marks them as read and sends them to your Trash folder. The reason you do this is because GMail account automatically delete anything in the trash folder after 30 days. This means that your trash folder will hold 30 days worth of backup emails before automatically trashing them for you, thus you never need to touch it.

What your GMail Trash folder will start to look like

What your GMail Trash folder will start to look like

Should you ever need to retrieve a backup, just head to your trash folder, and you’ll see 30 days worth of database backups for you to download and restore to your server.

Of course, you can perform this on multiple WordPress installations using the same mail account, as I do. You are only limited by the amount of space in your email account. However, even though I backup seven or eight databases, I still use just 1% of my GMail storage, so you’re not likely to encounter any problems.

Going the extra mile

I can’t say that I’m so paranoid that I’d suspect there’s a chance of my database being compromised and on the same day, GMail happens to lose all of my data, but if you really are that concerned, you could always modify your filter to forward each email to another email address hosted by someone else.

Alternatively, you could also do something more labour-intensive and just download the attachments every day.

Feel free to post any questions in the comments, and if you found this useful, let me know or share the article.

UPDATE: Bear in mind that if you install new plugins which create tables in your database, you will need to add them to the selection of tables to back up as new tables are not automatically added to the selection.

UPDATE II: As Paul rightly pointed out, you can achieve the same effect using another plugin – WP-DBManager – which also includes other functions, such as DB optimisation, repair and dropping/emptying tables from within WordPress. I have since changed from WP-DB-Backup to WP-DBManager, but understand that there is nothing wrong with WP-DB-Backup – it’s just that WP-DBManager does more. Thanks for the tip Paul.

Categories: Plugins, Security | Permalink

What next?

Hire me

If you couldn't quite manage this yourself, find it too intimidating, or just don't have the time to do it, you can always hire Dave to do it. Please get in touch so that we can discuss your needs.

Leave a comment

If you have a question, update, or comment about the tutorial, please leave a comment. I try and respond to every comment, though it may take a few days, so please check back soon.

Let a WordPress Expert help you

Do you want to try this, but feel like you need a helping hand, in case something goes wrong? My service, The WP Butler, gives you access to WordPress expertise whenever you need it. Better yet, I'll keep your site backed up, updated and secure, so that you don't have to worry about it. It's all part of the service. Use coupon DIWW and save 15% on all plans.

Visit The WP Butler

Author:

Dave has been tinkering with WordPress for many years, and he now shares his WordPress knowledge here on Do It WIth WordPress to help others realise its impressive power. He can also be hired to help with your WordPress needs. Dave, who is British, is married to his best friend, Marti, with whom he has a beautiful daughter, Ellie. When he's not dabbling with WordPress, he's probably eating Triscuits or hummus, watching an indie film or British TV show, spending time with friends or family, or exploring the world.

10 Comments

  1. Thanks for this post.
    I try this plugin and works fantastic!!

  2. I currently use WP-DBManager to manage and backup my database. I also use a separate account (from my blog) to send the database backups to for storage. Every once in a while I go through the backups and remove the backups that are several months old.

    In addition to the database backup, I also use WordPress Backup (by BTE) to backup my WordPress files – themes, uploads, etc.
    My recent post How To Disable Audio Enhancements in Windows 7

    • I\’ve actually been looking for a plugin that will backup the files also. I\’ve been relying on cron jobs and FTP to do the leg work for me, but I\’d rather do it the way you have described. I\’m going to be checking that out.

      Thanks for the information

  3. Hi Dave,

    I have just installed BackWPup on one of my sites. It can backup everything; database, files, plugins, etc. Whatever you wish to include in the backup. It has amazing options, including setting the frequency of backups and what time the backup takes place and, of course, the location for the backups to be sent. I backup to the server at the moment.

    It has only been active for a couple of weeks but so far is working a treat.

    Hope this is helpful.

    Cheers,

    Adrian Rice

  4. Thanks for the reminder Dave,

    I’m guilty as charged for not backing up my WP site and I’ve put so much time and effort into it that I’d be devastated to lose it. It’s been a nagging concern for some time and I did try it previously with a previous version of the site. It seemed like such a disjointed process and I wasn’t convinced I’d actually backed up everything – plus it saved it all to the host server.

    I far prefer your method of mailing yourself and therefore having the backup in a completely separate location to the hosting company. The last comment also has me intrigued though as it seems like a more complete solution. I feel more procrastination coming on…

    I’m from an IT background so know the importance of backing things up – I need to get this sorted and you’ve just given me a welcome shove in the right direction, thanks again & nicely done for the well written post,

    take care,
    Alan

    • Then you definitely have no excuse for not having a backup! I hope this encourages you that there are very simple and effective ways to backup your site on a regular basis.

      • Thanks Dave,

        I still haven’t set this up – but I will. I’m actually in the process (as we speak/exchange/write/whatever) of moving to a new hosting company (just changed the nameservers about an hour ago) so will try and set this up once all that is complete.

        I don’t suppose you could offer a kind of reminder service along with this backup solution, could you? (just kidding)

        Your site looks really cool by the way – you’ve got yourself a new subscriber,

        p.s. you may be able to advise me of one other thing – I always click the notify me of follow-up comments check-box because I want to see your reply.

        If I didn’t check that box would I still get notified if you replied to my comment but just not see other follow up comments or would I see nothing at all unless I check the box (& I’m assuming that’s a standard WP thing)?

        cheers,
        Alan

  5. Hi Dave,

    this was a useful post & thanks again – I have now set this up and have seen the exported database backup files coming through (.sql.tz).

    Do you have another post perhaps that advises how one would actually use these files to restore the database in the event of a crash?

    I have a test copy of my database and went to ‘Import’ but it does not have an option for SQL files, just WXR (WordPress) files or a number of other programs (blogger etc).

    How do I actually use the backup file now I have it?

    thanks,
    Alan

Leave a Reply