At WPBackItUp 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.
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 simple way to restore your database using WPBackItUp. All the steps we will outline in this article can be performed with a single button click using the WPBackItUp Premium plugin. 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.
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 WPBackItUp.
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 any version of WPBackItUp(including lite). 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”.
When you create a backup using the WPBackItUp plugin, a number of backup archives will be created, each containing different portions of your WordPress install. You will find your database backup in the "-main-" backup archive.
Download and open this zip file and you will find it contains quite a few SQL files. These are easily identified because they have a ".sql" extension. These SQL files correspond to tables in your WordPress database and there will be at least one SQL file for every table.
You may have noticed that each of these files contain a number towards the end of the file name.
This number represents the export sequence for the table and will be incremented for every 10,000 rows of data. So if you had a table with 20,000 or more rows of data then you would see SQL files ending with "-0.sql" , "-10000.sql" and "-20000.sql" and so on, as seen in the example below.
If you have a relatively small database then it is likely all your SQL files will end with "-0.sql". We break the database export into multiple SQL files to support customers with large databases. This helps to prevent long running imports that can timeout or fail because of their size.
All of these SQL files makeup the content of your WordPress site. This 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 single SQL file that can also be used to restore your database.
Before you proceed to the next step why don't you open one of these SQL files to see what it contains. You can view these files 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 any of these files are empty then your database backup may be 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 backup will do this for you but just to be thorough I am going to outline the steps needed to remove your old content. You may skip this step if you are certain your backup performs the cleanup for you. SQL files created using WPBackItUp will perform this step 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 the 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 you can restore your WordPress database you 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 be prompted to make 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: Importing Your Database
The last step is to import your database using phpMyAdmin. phpMyAdmin makes this very easy for us by providing an "import" option but there are file size restrictions that may make it necessary to import each SQL file separately. Next we will outline how to combine all your WPBackItUp SQL files into one file but first lets review how to use the import feature in phpMyAdmin.
To begin, select the “import” link at the top of the page. The import page (shown below) will allow you to upload all of the SQL files we identified in step 2.
Click the “Choose File” button and navigate to the first SQL file. In our example we will use a file named "db-backup.sql". If you have created a manual database backup then you may only have one SQL file, which may also be selected. Below you can 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.
This step must be performed for every SQL file contained in your backup. It is extremely important that you import all the "-0.sql" files first because they contain the SQL statements needed to create the tables. All of the other SQL files only contain data.
Please take note of the upload file size limitations shown below. This may prevent you from importing large SQL files.
To work around this phpMyAdmin limitation, you may compress your SQL file using a gzip, bzip2, or zip file using a compression utility. The compressed file may be uploaded as outlined above.
Step 5: Merging the SQL files
If you have a small database and would like to combine all your SQL files into a single file, then this may be accomplished with a text editor or from a command line. To do this using windows, open up a command window and navigate to the folder that contains all your SQL files. Enter the command below and all the files containing a ".sql" extension will be combined into a single file named "db-backup.merge".
copy /b *.sql db-backup.merge
* There are similar commands for Mac and Linux platforms.
If this is performed correctly you should see a list of all your SQL files followed by " 1 file(s) copied".
You should also see a new file named "db-backup.merge". Following the instructions in the step above, import the "db-backup.merge" file and your entire database will be restored. Please note that there are phpMyAdmin file size limitations that may prevent this but you will see an error message when this occurs.
Step 6: 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 7: Feedback
If you found this post helpful click "Yes" below or let us know via https://www.wpbackitup.com/contact/ We love hearing from customers.