WordPress Database Backups: The Definitive Guide

Database backups of your site may seem like a good idea in theory: 

• They take up less storage space
• They contain the most recent changes
• They allow for more granular restores

But are they really?

They are not. These so-called benefits are actually problems in disguise. Specifically, storage problems, frequency problems, and reliability problems. 

The truth is that database backups are partial, incomplete backups. These will always cause more problems that they solve. 

Therefore, always take full backups of your site. 

Made changes to your site that you regret? Updated a plugin that crashed your site? Have you been hacked and lost precious content? 

The solution is a full site backup that can be partially restored. 

TL;DR: Use BlogVault, the best-in-class WordPress backup plugin, to take backups of your site. Whether you want to restore a plugin, some content, a theme, or even the whole site, BlogVault has you covered. All the benefits of backups with none of the issues.

What does WordPress database backup contain?

The database of a WordPress contains all the content that makes a site unique.

This includes posts, pages, widgets, plugin settings, theme settings, and other user-generated content. This is all crucial information for your site to function.

Because this content makes a site unique, much advice online will tell you that backing up a database is sufficient insurance. 

It isn’t. 

More often than not, site files also contain unique information about the site. Like the theme. Custom themes aren’t saved in the database; they are only present in the files. 

Therefore, while we will talk about the various ways to backup WP databases, suffice it to say that a full site backup is really the only way to go.

Option 1. WordPress database backup plugin [recommended]

The easiest way to get a WordPress db backup is to use a backup plugin. 

Install BlogVault for a full backup, and then extract the database from there. 

• Storage is not a problem, because all backups are stored on BlogVault servers.
• BlogVault syncs backups automatically, so the most recent changes are saved. 
• You can partially restore any file or database table from a BlogVault backup. 

Plus, it’s far less risky than a manual database backup with phpMyAdmin.

1. Install and activate BlogVault: As soon as you connect your site to BlogVault, it will sync your site. Within minutes, your backup is ready.

2. Download of the backup: Once a backup is done, check the Details in the Backup section of your site dashboard. Here, you’ll find your latest backup, and the option to download it.

3. Select files to download: You can either download the whole backup, or just the database tables. 

Alternatives to plugin backups

🚨 There are a few ways to take a backup manually. We’ll go through each method carefully, however please note that none of these methods guarantees an error-free backup like a backup plugin does.

Option 2: Backup WordPress database using phpMyAdmin

To get a WordPress database backup from phpMyAdmin, you need to export the entire database into an SQL file. 

You can access phpMyAdmin in a few ways: from your cPanel; by appending /phpmyadmin to your site URL; or from your web host’s dashboard.

1. Log into phpMyAdmin: Look for credentials with your web host, or in your wp-config file in a pinch. 
2. Locate the right database: Click the Database tab. Select the database you need to export.
   
   One phpMyAdmin interface can have databases from various sites. To find out which is the right database to export, look for the database name in the wp-config.php file. 
3. Export the files: Select all the files and click Export.

This tutorial is a subset of taking a site backup using phpMyAdmin.

📝 If your web host doesn’t provide phpMyAdmin, they may use another database management tool like Adminer. Either way, the steps are mostly similar. 

Option 3: Using cPanel Backup Wizard

If you aren’t comfortable messing about with phpMyAdmin—we know we aren’t—then you have the option of using cPanel. 

A fair few hosts use cPanel, which has a Backup Wizard feature. Downloading the WordPress database from there is super easy. 

Here’s what you need to do:

1. Go to the Files section of your cPanel and click Backup Wizard. 
2. Click cPanel Backups in the Other cPanel Backups section.
3. In the Partial Backup section, find Download a MySQL database and click the name of the database. 
4. Give it a minute and it should download as a .sql.gz file.

🚨 Downloading a database backup this way is super easy. However, restoring a large database (anything over 500 MB) will fail. cPanel is just not designed to handle the transfer of large files correctly. 

Option 4: Backup WordPress database using SSH

For the command-line experts here, you can use SSH to export the database to an SQL folder as well. 

You will need to have WP-CLI installed to do this.

1. Connect to your site server using SSH: In most cases, the credentials will be the same as your FTP ones. 
2. Navigate to the wp-config.php file: This is most likely in your site’s root directory. Execute the following command. Replace filename with an appropriate name for your database export.

wp db export filename.sql

3. Download the file to your local machine: Exit the server and download the database file using the following command:

scp username@serveraddress:rootfolder/filename.sql localfolder

Option 5: Using the mysqldump utility

phpMyAdmin can’t handle backing up large databases. For such situations, you can use MySQL commands. It’s an efficient, secure, but complex way of using mysqldump utility. 

It requires adding custom code to the command line. If not, here are the steps to exporting your database:

1. Connect to the server: Login to your server with SSH. Replace username and ouripaddress with your username and actual IP address. Then add the password when prompted.

ssh username @ouripaddre

2. Navigate to the right location: First, navigate to the correct directory using the following command. Replace the text after cd with the actual path.

cd /path/to/your/site/directory/MYSQLDB.com/tables

3. Exporting the database: Use the mysqldump utility. The command is as follows. Replace username with the correct username, database name, file name and the format you’d like it to export as.

$ mysqldump -u USER_NAME -p DB_NAME >file_name.format

Why backup your WordPress database

Once again, we don’t recommend that you only backup your database. A full site backup is always the way to go. 

Having said that, let’s talk about why you’d want to backup your database (and files) in the first place:

1. Restoring a crashed site: Plugins and themes updates are notorious for potentially crashing your site. You always need a backup that you can restore in case your update is incompatible. We recommend having a backup before and after any changes you make to the site.
2. Reversing a wrong: Maybe you’ve realised that the new site design is bringing in less conversions than the previous one. Maybe, you’ve accidentally deleted a whole page. Whatever is wrong, a backup restore can make it right.
3. Malware infections: Some malware hacks can deface pages on your site. Security plugins like MalCare can definitely clean your site and get rid of the malware. But it cannot restore your site to its previous glory. That’s where backup restores come in.
4. Migration: If you’ve managed a site before, you know that there are many reasons why you’d need to migrate to a different host or server. Maybe your server can’t handle the increased traffic that you have. Maybe you’ve noticed that a different host has better features or support. Either way, to migrate a site, you need to export your entire site and import it to your new host or server.

Whatever your reason for a backup, we recommend using BlogVault. You can automate, schedule and have real time backups for your site so you always have the newest version to restore. The restore process only requires a few clicks. With BlogVault, backing up a site has never been easier, safer and more assured.

WooCommerce database backups are a special case, and should be treated as such.

Potential issues with database backups

Let’s look at why database backups cause more problems than they solve. 

Myth: Easier to restore compared to a full backup

If you’ve only taken a WP database backup, you’ll be unable to restore your full site because a restore requires both files and a database. 

Even if you just want to restore content, many of the manual methods we’ve listed above cannot handle large databases. Sure they can download them, but they cannot upload them. 

Myth: Database backups don’t load the site

Backing up your site database is as resource-intensive as a full backup. Timeouts and downtimes are common. 

What you need is an incremental backup. A main full backup, and a system that intelligently saves only changes thereafter. 

BlogVault has perfected incremental backups for WordPress sites.

Myth: Database backups save on space 

Well, what if you have a large site? A database backup can be a few GBs and that can take up valuable real estate. 

With BlogVault, your backups are stored on Amazon S3 servers.

More issues with database backups

Failed backups: Something as simple as losing connectivity to your server can cause a partial or failed backup. This is especially probable when you’re using a manual backup method for your database. The worst part? You may never know it was a failed backup unless you test the restore.

Time-consuming: This is especially true for larger sites. It takes a long time for your entire database to be backed up and downloaded to your computer.

Final thoughts

While there are so many reasons to backup a database, it is essential to take a full backup of your full site. The best way to go about doing so is to use a backup plugin like BlogVault. 

With BlogVault, backups are automatic, can have a custom schedule and taken in real time. In other words, with BlogVault, you never have to worry about backups again.

FAQs

How to backup WordPress database?

The most efficient way to take WordPress database backup is to use BlogVault, a trusted and easy-to-use backup plugin. Install the plugin and sign up. It will automatically take a full backup but you can download just the database if you want. Here are the steps to do so. 

How to take a full backup of your site?

Use a WordPress backup plugin like BlogVault to back up your full site. Just install the plugin and sign up. It will automatically take a full backup. 

How often should you take a backup?

We recommend taking a backup daily, and before updating anything on your site: WordPress versions, plugins, themes, etc. If you’re a WooCommerce site, take real-time backups. BlogVault takes real-time ones and automatically takes a backup before you update a plugin or theme (if you’re using the BlogVault dashboard to update them).

How can you schedule backups?

You can schedule backups using BlogVault. Once you install the plugin, you should be able to access the site dashboard on BlogVault. On the left, you see a site overview. Select the hamburger icon below the site name. Next, click Sync Time and select what time of the day you’d like to schedule to sync your site.