This video shows a more advanced backup method, which is to backup directly to another MySQL database.

MySQL Database

You also have the option to backup directly to another MySQL Database. This could be useful if you run a testing environment on the same server as your live site. You could potentially clone your site code and files to another directory, then setup an empty database, and use Backup and Migrate on the live site to fill in that new database with a copy of the live database directly. No need to export the database, and then import it to the new site. All you need to have is the host, database name, database username and password and you're good to go.

I'm going to demonstrate that now.

First, I'll open up phpMyAdmin and create a new database

  • Open Acquia Dev Desktop Control Panel
  • Click "Manage my database
  • Click "Databases"
  • Create database: testbamsite
  • (Create)
  • Click the "Check Privileges" button next to the "testbamsite" database

Here, you see that the user "drupaluser" has all privileges. This is the default user created by Acquia Dev Desktop. Because there are so many ways to add databases and users across server configurations, this might be different on your server, but once you have the host, database name, database username and password, you can proceed.

Here's the default information used by Acquia Dev Desktop (you can find this information at the bottom of the settings.php file in your "sites/default" folder.)
* Host: 127.0.0.1:33066 (It is important to know that Acquia Dev Desktop runs MySQL on a non-standard port so that it won't interfere with other server setups. i.e.: MAMP, WAMP, XAMP etc.)
* Username: drupaluser
* Password: [no password]

If I go back to my database list, and click the database my Drupal site is using, you'll see it has all of the tables necessary for my site to run. If I go back and choose the database I just created, you'll see that it is empty and has no tables.

Let's go ahead and set this up.

  • Go to "Configuration => System => Backup and Migrate => Destinations" (admin/config/system/backup_migrate/destination)
  • Click "Add Destination"
  • Choose "MySQL Database"
  • Destination name: Test Site Database
  • Host: 127.0.0.1:33066
  • Database name: testbamsite
  • Username: drupaluser
  • Password: [none]
  • (Save destination)

Now we're ready to test it to make sure it works.

  • Click the "Backup" tab (admin/config/system/backup_migrate)
  • Backup to: Test Site Database
  • (Backup now)

That's all there is to it! If we head back to phpMyAdmin and click the "testbamsite" database, we'll see that it is now populated with the tables and data from our live site.

You'll also notice that the "Test Site Database" is an option to backup from. This just shows how you can backup to as well as from multiple databases using Backup and Migrate.

So, that's how you can auto-populate another database with the tables and data from another site.

One thing to keep in mind when using this method is that Backup and Migrate will add new tables, and replace existing tables, but will not delete unused tables. So a good practice would be to drop all tables from the target database before performing the backup.

This can be done in phpMyAdmin the following way.

  • Select the database from the left-hand column
  • Make sure the "Structure" tab is selected at the top
  • Scroll down and click "Check All"
  • Choose "Drop" from the drop-down
  • Click "Yes" (If the database is large, you may need to select all and choose drop multiple times)

That's it! Now it's empty and ready to be populated by Backup and Migrate.