Instructions: Restoring WordPress Backup – Database and Files

WordPress first aid

Performing routine backups is essential, and most WordPress users are aware of that. But what do you do when disaster strikes? How do you copy the backup onto the server again? Our instructions will show you how – step-by-step.

Note: It is worthwhile to have already tried out the tutorial prior to a potential failure of your website, on a test system, for instance. Then you’ll be considerably more composed. At any rate, your emergency bookmarks should include a link to the instructions.

In case you’re “in a hurry”, here are shortcuts to the individual methods:

  1. Restore database with phpMyAdmin
  2. Restore database with Adminer
  3. The Adminer plugin for WordPress
  4. Restore database with MySQLDumper
  5. Restore database with FileZilla

However, please also note our instructions on Preparation of the database for a dual backup.

Restoring a database versus files – what is the difference?

First of all, you should distinguish between the following: Do you want to

  • restore a database (containing all blog articles, pages, products of your WordPress shop…) and/or
  • the files of the WordPress installation (incl. your uploaded images, etc.)?

Most backup applications – such as our Plugin BackWPup back up both, if you have configured them that way (perhaps now would be a good time to check on this).

Note: The export module from WordPress is no substitute for a complete backup solution. It only exports individual elements from the database. Important information on theme or widget content, for example, is not backed up, nor are the files of the WordPress installation or your uploaded media.

You typically restore a database backup if your WordPress installation is no longer working properly or you can no longer log in, and an appropriate error message is generated (example: “Error in establishing a database connection”). Before restoring the backup, however, check to see whether there is another cause for the error:

  • Is the database server or your provider currently not available?
  • Have you stored the wrong access data in the file wp-config.php?
  • Can the database be repaired?

If the complete content of the web server is lost, or if you are required to restore the entire system – after a “hack” into your blog or shop, for instance – then you have to upload the file backup. We’ll explain both scenarios to you.

Restore database – preparation

Most backup services and plugins generate a ZIP file. You extract this locally on your computer. In it, you should find a file with the extension “.sql” or “.sql.gz” – that is the content of your data backup.

A sample data backup - with the searched SQL file
A sample data backup – with the searched SQL file

In this segment, we will show you a method with which the table prefix of the database tables is modified in such a way that new tables are created during the subsequent import. This enables the existing database contents to be preserved – a safety measure, as your backup could be faulty and/or out of date as well. If you should discover that after successfully restoring the files, you can still access parts of the old tables.

Note: For large databases (from roughly 100 MB, depending upon your system), you may have editing difficulties when changing the table prefix. In that case – or if you wish to restore the database without the extra procedure of creating new tables – simply omit the following steps.

To modify the prefix, proceed as follows:

  • Open the file wp-config.php in your data backup with a text editor. Or download it from your server by means of FTP if you have only made one database backup.
  • Search there for the line $table_prefix = 'wp_'; (the prefix may also be named differently than wp_).
  • Modify the wp_ to wpnew_ (or in any other random, yet unique, abbreviation) and save the file.
  • wp_new

  • Open the SQL file of your database backup, also using a text editor.
  • Execute a search-and-replace query in the editor: In doing so, replace each wp_ with wpnew_ (or with whatever your new abbreviation may be).
  • Save the SQL file, and modification of the table prefix is complete.

Important: Please remember the following for this version: After restoring the database, you still have to upload the modified wp-config.php to your web server, so that your WordPress installation will access the new tables.

Now you can begin with the actual restoration or import of the data. This scenario uses phpMyAdmin, which most of the webspace providers have preinstalled as a tool.

Restore database with phpMyAdmin

First log in to your phpMyAdmin account.

  • For Strato servers, this is possible under Your server → Administer databases → Main phpMyAdmin login.
  • At 1&1 under Hosting → Administer Webspace → MySQL database → phpMyAdmin.

For all other providers: Please consult with your provider directly. Depending upon the phpMyAdmin version, the whole procedure works as follows:

The main interface of phpMyAdmin
The main interface of phpMyAdmin
  • From the left-hand list, select the database you wish to restore, in case there are several databases or WordPress installations on your server (with a click on the database name db00xyz).

    Tip: In case you are unsure here as to which database is involved: The database name is also listed in the wp-config.php, in the row define('DB_NAME', NameOfDatabase');.

  • In the menu bar above, go to Import.
  • Click on the Search button. Navigate to the extracted SQL file from your data backup that you wish to restore.
  • As a general rule, the other miscellaneous values are properly preconfigured. In terms of Character set of the file, “utf” or “utf-8” are normally used. Some databases are also encoded as ISO-8859-1 (Latin 1). You would recognize an incorrect format after import due to errors in displaying special characters in your blog articles.
  • The format must be set to SQL, unless your backup solution stored an XML file.
  • With a subsequent click on Go, restoration is launched.

Wait until – hopefully – the message appears indicating that the import has been successfully completed. Now your database, and your website, should be functional again. In case you modified the table prefix, it is necessary for you to first upload the modified wp-config.php file onto the server. In this case, you can also delete the old tables, if you are sure that all new uploaded contents are working and up-to-date (in phpMyAdmin under the item “Structure”, select tables with the old prefix, and choose “delete” at the bottom of the selection field).

With some providers, only databases up to a particular file size can be uploaded at once. If you import a larger file, you’ll receive a corresponding error message. Then you can use a so-called SQL splitter, which divides up the SQL file into several files. Such as the SQLDumpSplitter for Windows. For Linux or Mac OS please read these instructions.

SQLDumpSplitter
The SQLDumpSplitter

Caution: Be sure to obtain proper information beforehand on the SQL splitter you wish to install. There are splitter tools that contain malware or useless additional software. In case your provider only allows small phpMyAdmin import packets, a high number of small individual packets will be made out of large databases (from roughly 20MB). Then you should use Adminer or the MySQLDumper to complete the restoration, as long as the web host allows access to the relevant MySQL database.

With SQLDumpSplitter, an additional file is generated, which contains the data structure of the table (*DataStructure.sql). This must be imported first. This is followed by the individual split SQL files – in the sequence of the file name numbering. For other tools, you will find more detailed instructions in their respective descriptions.

Restore database with Adminer

Especially for larger databases, it is usually significantly more convenient to restore them using Adminer (formerly phpMinAdmin). It is a free tool for administering databases which consists of a single PHP file, adminer.php. You download it here, copy it onto the web server (using FTP, see here) and launch it from there. If you store Adminer in the main directory of your server, it is sufficient to simply enter www.yourdomain.de/adminer.php in the web browser. Subsequently, the following login window appears:

The Login Window of Adminer
The Login Window of Adminer

Here you can log in with the user data of your database account. You’ll find these details in the user account of your server, they are also contained in the wp-config.php file of your WordPress installation. With some providers, it is necessary to enter the actual server address instead of “localhost”. This should also be saved in the server user account.

After logging on, you have access to your database, the individual tables as well as the menu item “Import” (in the upper left-hand sidebar):

The Database View of Adminer
The Database View of Adminer

If you click on this menu item, you are directed to the import function with which the database backup can be restored:

The Import Function of Adminer
The Import Function of Adminer

There are two options for this function:

  • With File upload, you can select the database backup stored locally on your computer (as in SQL or a compressed GZIP file), and this is copied into your database by clicking on “Execute”. Caution: The existing server database and the tables contained in the backup will be overwritten in the process. Where necessary, as a first step, please bear in mind our explanations under Preparation of the database.
  • Alternatively, by means of From server, a backup can be restored that you previously uploaded to your web server via FTP. In doing so, the backup must be located in the same directory as the file adminer.php, and renamed to adminer.sql or adminer.gz. If there is an error during the restoration of the backup via “File upload”, due to a server timeout or low upload speeds of your Internet connection, for example, then try using this alternative method.

You can deactivate the checkbox “Stop on error”, if

  • remaining records are to be manually imported if import is terminated
  • the import generates database errors that are negligible

It is worthwhile in this case to leave “Show only errors” checked. As otherwise, you have to scroll through a large number of positive status messages in order to pinpoint any errors.

Important: After using Adminer, you should delete the adminer.php file from the server for security reasons, along with any backup you may have uploaded there (when using the import version “On the server”). Or you can also move them into a password-protected server area.

The Adminer plugin for WordPress

Some providers don’t allow you to connect with the server database from your local computer. This is the case, for example, with individual Strato PowerWeb packages. Then when logging on to Adminer, an appropriate error message is generated (e.g.: “Can’t connect to local MySQL server through socket /tmp/mysql.sock”).

This is where our free plugin Adminer for WordPress comes in handy. It links the control of adminer.php to the back end of WordPress, thus circumventing the access lock. To complete this operation, install the plugin and after activation, launch Tools → Adminer (in the sidebar of the WordPress back end):

The Adminer WordPress Plugin
The Adminer WordPress Plugin

No additional configuration is necessary, as the plugin retrieves the login data directly from your WordPress installation. One click on “Start Adminer inside” or “Start Adminer in a new tab” is sufficient to launch Adminer itself. Now you can proceed as described in the previous section.

Tip: If you wish to use the Adminer import function “On the server”, the files to be imported must be contained in the plugin directory ..wp-content/plugins/adminer/inc/adminer/

Restore database with MySQLDumper

MySQLDumper is a backup program for MySQL databases, which also enables easy and quick restoration of backups. And this is independent of whether they were created with MySQLDumper, as long as they are in an SQL standard format. The tool is also installed on the web server:

  • Download the latest version here (under “Download”) and extract it locally on the computer.
  • Change to the extracted folder msdx.x.x, and copy all directories and files via FTP into a new subdirectory of your web server.
  • Launch the install.php installation routine. If the uploaded data is in the server directory “mysqldumper”, you simply enter www.yourdomain.de/mysqldumper/install.php in your browser.
  • Select the desired language and click on “Installation”.
  • Enter the database parameters: Database hostname, user, password and, where necessary, the database name itself (for more information, see explanations under “Restore database with Adminer”). Then click on “Connect to MySQL”.
    mysqldumper
  • If the connection is successfully completed, “Save and continue installation”.
  • Now the main administration interface of MySQLDumper appears, which can be repeatedly called up at any time by entering www.yourdomain.de/mysqldumper/:
    mysqldumper home

You copy the database backup you wish to restore into the subdirectory ../work/backup/ of MySQLDumper (thus in our case study, this would be /mysqldumper/work/backup/). During an initial installation of the tool, or if the backup itself was not generated with MySQLDumper, this directory must first be created manually (using FileZilla, for instance, more on this topic later).

Now you navigate in the MySQLDumper menu (in the left-hand sidebar) to the item “Restore”. If the database to be imported – as just described – was properly saved, copied, a link will appear there with a reference “Backups of other scripts”:

Restore database with MySQLDumper
Restore database with MySQLDumper

Now you choose the file to be restored, and you confirm the upper left-hand “Restore” button:

restore final

It’s necessary for you to confirm this once with “OK”; in addition, you will also be queried as to the encoding of the backup file (as a general rule, this is utf8). And now the restoration process can begin. Please also note here that your old database or already existing tables will be overwritten.

Note: The function “Choose tables to be restored” can only be used if the backup was also generated using the MySQLDumper.

Important: MySQLDumper should be installed in a password-protected area of your web server. The tool offers a separate option for this, “Create directory protection”. Please note that this may lead to problems for certain server constellations. As an alternative, you can remove the entire MySQLDumper directory as soon as the database has been successfully restored.

Restore files with FileZilla

After copying the database, now the key is to restore all the files of the WordPress installation. This step is only necessary if the data on the web server have been completely lost or if despite a database repair, not all areas of the blog are functioning (e.g. article images, functions controlled by plugins, elements of the blog design, etc.).

The files saved in your backup are uploaded via FTP (file transfer protocol, a file transfer method) onto the web server.

Note: In doing so, any existing contents are overwritten. In case you are not sure whether your backup is up-to-date, and there are still files remaining on the web server, you can initially transfer them over to your computer (also via FTP). Different status conditions between the backup and the actual status of the server can thus be corrected after the fact. Naturally, this does not apply if your WordPress version has been hacked (risk of viruses and malware).

The most common FTP client is the FileZilla freeware application. It must be installed locally on the computer. This link takes you to the download of the client version for Windows, Mac OS X and Linux. Afterwards, to restore your file backup, you proceed as follows:

  • Extract the ZIP file of the data backup into a directory on your computer.
  • Set up FTP access in the user account of your server (your provider should make the instructions for this available to you).
  • Launch FileZilla.
  • Go to the top menu item File → Site Manager. In the opened menu, click on New Site.
    new Filezilla site
  • Now you can enter the server (host) and user name, as well as the FTP password in the relevant fields. The remaining fields can generally remain empty. Then click on “Connect”.

    Important: If possible, use a secure FTPS/SSL connection protocol, instead of the preconfigured setting “FTP”. Practically all web hosting companies use their own versions. Ask your provider for the correct procedure. In case your provider does not support SSL, change your FTP password prior to creating the connection (in the user account of your web server). Then use this temporary password only for restoring the backup, and reset it again afterwards.

  • In the left-hand corner (below “file name”), you now see the local content of your computer, and on the right, that of the web server. Navigate in the left-hand side to the folder in which your extracted backup of WordPress files is located. It should appear as follows, and as a general rule, begin with the folder “wp-admin”.
    wp
  • Now highlight all the folders and files of your backup. Click once on the top entry (in this case “wp-admin”), scroll down, hold down the shift button, and click on the last entry. Now all the folders and files are highlighted in blue.
  • Click on the left-hand mouse button in the blue area, hold down the mouse button and drag the files with the cursor into the right-hand server window.

Now FileZilla will begin copying. In case there are still files on the server, you will be asked whether you wish to overwrite them (see the note at the beginning of this section). If all contents have been successfully moved, then your file backup is restored. Your blog or your shop should now function normally.

We will be happy to answer any general questions regarding this tutorial in the comments. In case you are aware of additional options for restoring a backup, we look forward to your feedback as a comment.

Title image: © Felix E. Guerrero License: CC BY-SA 2.0

Post Sharing

Author Avatar

Editor at MarketPress. Passionate blogger, corporate blog expert and book author (e.g. "Blog Boosting"). Co-organizer of WP Camp Berlin.

Similar Posts

slack-blog

New Plugin: Slack Connector - Connect WordPress, WooCommerce and Slack

by Michael Firnkes

Initially we merely wanted to optimize our own Slack-processes. With automated notifications from our MarkettPress shop, the blog and our forums. The resul ...

Read more
vip

Inpsyde is the first WordPress.com VIP partner in Germany

by Michael Firnkes

Automattic has given us the title of WordPress.com VIP Service Partner, the first in Germany, Austria and Switzerland. Worldwide, there are only eleven com ...

Read more
wooexperts

Inpsyde is a WooExperts Gold Partner of WooCommerce

by Michael Firnkes

A few weeks ago, Inpsyde GmbH with MarketPress became a certified WooExperts Gold Partner. This is confirmation from WooThemes of our agency’s know-h ...

Read more
backwpup

BackWPup Pro: Secure WordPress Backup with Google Drive

by Michael Firnkes

The Pro version of our BackWPup plugin supports the backup of WordPress databases and files to Google Drive. But how do you set something like that up? And ...

Read more

Comments

2 Comments

  1. #1

    Okay, so all this is new to me. Got hijacked.
    Found my backup files done with Backwpup. Couldn’t restore easily. Grrr!
    Tried many methods, and in desperation tried to use the mysql method you outlined.
    Changed name to wpnew_ in old php file. When I did a search and replace on backup – nothing showed up to replace.
    Imported backup.
    Now I’m really lost. Didn’t understand that part about upload the modified file onto server. Huh???
    So of course there’s an error establishing database connection.
    Love this article, and feel I’m only a couple clicks away from my very first restore!!
    Can you help please?

  2. #2

    I need serious help, I need to restore my backup to a new server. I need someone that can help out as my host says they don’t do data migration

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">