Export and import Drupal database with drush command

23 Oct 2020

Drush is the ultimate tool and companion for Drupal and it does a great job in situations where we want to import or export our Drupal database.

Export/Backup Drupal database with Drush

The best way to export the Drupal database is to first rebuild/clear the cache first and then export DB with Drush sql-dump command. Clearing the cache first will empty the cache tables in the database and significantly decrease the database size.

Export Drupal 8 or Drupal 9 database

To export Drupal 8 or 9 database, the first step is to rebuild your cache before database export
```
drush cr
```
Now run the drush sql-dump command with the destination argument to export the Drupal 8 database to the desired location. This will dump the database into a gzip file without cache table content.
```
drush sql-dump --gzip --result-file

drush sql-dump --gzip --result-file=/path/to/file.sql
```

Export database in Drupal 7

For Drupal 7 also, you need to clear the cache first to empty the cache from the database.
```
drush cc
```
Now run the drush sql-dump command to export the Drupal 7 databases. The export will be a gzip file without cache table content.
```
drush sql-dump --gzip --result-file

drush sql-dump --gzip --result-file=/path/to/file.sql
```

Import Drupal database with Drush

The below drush command can be used to import an SQL dump file into the Drupal database. Make sure to drop the database first and then run the import command which will import the data to Drupal's current database.

drush sql-drop -y
drush sqlc < ~/path/to/db-file.sql

More resources:

Drush sql-dump command - https://drushcommands.com/drush-8x/sql/sql-dump/
Drush sqlc command - https://drushcommands.com/drush-8x/sql/sql-cli/
Drush sql-drop command - https://drushcommands.com/drush-8x/sql/sql-drop/