Automate WordPress Database Backups for Care-Free Protection

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

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.

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

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

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

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.