How to Manually Restore Your WordPress Database
At WP BackItUp we are working really hard to make backing up, restoring, cloning and migrating your WordPress websites as simple as possible. In most cases we’ve been able to turn all the heavy lifting into a single button click right from your WordPress dashboard.
If you are one of those brave souls that want’s to do all that heavy lifting yourself then this article is for you. In this article I am going to outline all the steps required to manually restore a WordPress database. This article assumes you have a backup of your database either created manually or using a backup tool like WP BackItUp.
Now let’s get started!
Step 1: Get Out and Stay Out
To begin you will need to log out of the WordPress dashboard and stay logged out.
This is important because WordPress will attempt to recreate your content database if it detects something is out of order with the existing database. WordPress performs this check whenever the site is accessed so while we are performing the restore it is crucial that you do not login to the WordPress dashboard until we have finished.
Step 2: Find Your Database Backup
Before we go any further it is extremely important that you make sure you have a backup of your database. Database backups can either be created manually using a MySQL admin tool like phpMyAdmin or by using WP BackItUp. If you would like to learn how to create a manual backup of your database then check out the article “How To Manually Backup Your WordPress Site”.
If you created your backup using WP BackItUp then you will find your database backup in the root of your backup archive. This file is named db-backup.sql and can be seen in the example below.
WP BackItUp’s db-backup.sql file contains all the contents of your WordPress site which includes all your posts, pages, comments and WordPress settings. If you performed a manual database export using a MySQL admin tool like phpMyAdmin then you will have a similar SQL file that can be used to restore your database.
Before you proceed to the next step make sure you have found your database backup and that it actually contains a backup of your database. This can be done by opening the SQL file with any text editor. Once opened you should see a large number of SQL statements containing the database definition (schema) and your site content. If this file is empty then your database backup is corrupt and you should not proceed to the next step.
Step 3: Preparing your Database
Before we can restore your database we must first clean up all the old content. In some cases your database backup will do this for you but just to be thorough I am going to outline the steps needed to cleanup your old content. You should perform this step unless you know for sure that your backup performs the cleanup for you.
Working with your WordPress database is very easy with all of the mySQL Admin tools. Your hosting provider will provide access to one of these tools via their control panel. Please contact your host if you are not sure how to access your mySQL admin tool. In this article we will use phpMyAdmin but all of the mySQL admin tools have similar functionality.
Using PhpMyAdmin you must first navigate to your WordPress database. This can be done by clicking the "Databases" link shown below. Once selected you will see a list of all database that you have access to. In the example below only the “wp_demo” database is available.
You can navigate to your WordPress database by clicking the database name, in this case “wp_demo”. If you are unsure of your database name then it may be found in your wp-config.php file which is located in the root folder of your WordPress install. Below is an example of the database configuration section in the the wp-config.php file. The “DB_NAME” setting contains the database name which in this case is “wp_demo”.
Once you have selected your database all the tables that are contained in your WordPress database will be listed. Below is an example of the core WordPress tables, however, your database may contain more than are shown below. Before we can restore your WordPress database we must first remove all the existing tables.
To remove all your existing tables select the “Check All” option and then “Drop” from the pull-down.
You will prompted to be sure you want to execute the query. Clicking “yes” will delete (drop) all the tables in your WordPress database.
After the query has completed you should have an empty database that will look like the example below.
Step 4: Restoring Your Database
The last step is to import your database using phpMyAdmin. To do so, select the “import” link at the top of the page. The import page (shown below) will allow you to upload the SQL file we identified in step 2.
Click the “Choose File” button and navigate to your SQL backup. In our example we will use the db-backup.sql file that was created by WP BackItUp. If you have created a manual database backup then you may select that SQL file. Below you see that the db-backup.sql file has been selected.
Leaving all the other options set to their defaults, click the “Go” button. Your database backup will be uploaded and imported, restoring your database to its backed up state. If everything goes as expected you should see the success message as shown below.
Please take note of the upload file size limitations shown below.
If your database backup exceeds the maximum file size listed then you will need to compress it into a gzip, bzip2, or zip file using a compression utility.
Step 5: Validating the Restore
The last step is to make sure your WordPress database was restored properly. To perform this step simply click on the “Structure” Link at the top of the page. If everything went well you should see the following tables listed. There may be more tables than what is shown below but there will never be less.
Step 6: Restoring your WordPress Database the Easy Way.
While we appreciate your desire for hard work and adventure we want to let you know that there is a much simpler way to restore your database. All the steps outlined in this article can be performed with a single button click using WP BackItUp. Simply click the “Restore” link as shown below and your database, plugins, themes and media files will be restored instantly. No CPanel, PhpMyAdmin, or FTP required.