How to restore a database dump with drush.

`drush sql-connect` < example.sql will import any SQL file such as one saved by drush sql-dump. Put your own path and filename where example.sql appears. Those backticks at start and end are important.

This also works:

drush sql-cli < example.sql

Note this does not restore but "dumps into" (don't know if it's a correct expression). I mean, if you want a full restore you previously need to drop and recreate the database, or drop each table.

in that case, use site-install or if that is too slow, write patches for the installer :)

I traditionally do use this in a local environment, with credentials in ~/.my.cnf

mysqldump db > db.sql
mysql -e 'drop database db; create database db';
mysql db < db.sql

For a drush command it should drop the tables instead of the database, because of permissions.

jonhattan wrote:

This also works:

drush sql-cli < example.sql

Note this does not restore but "dumps into" (don't know if it's a correct expression). I mean, if you want a full restore you previously need to drop and recreate the database, or drop each table.

My understanding is that it's not necessary to drop the database or tables before restoring from a "dump" file, because the dump file contains instructions to drop the tables first and then recreate and populate them, for example:

DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
...etc.

I've been just shoving dump files in on top of existing databases for a couple of months now, using "drush sql-cli < example.sql", and it seems to work OK...

If you add some tables to your database by installing a module or something and then you restore a backup using drush sql-cli < example.sql, those new tables will be orphaned. Whether that creates a problem for you depends on your particular setup.

with that said, I do this if I haven't added tables:

cd backup_migrate/manual/
latestfile=$(ls -t1 | head -n1)
drush bam-restore db manual "$latestfile"

As this page is the first result when you search in Google for "drush restore dump" I'm going to add a comment for the current drush version.

With drush 9 this can be easily done with:

drush sql:drop
drush sql:query --file=20200330_135446.sql.gz

Hope this help anyone that lands in this page in 2020 :)

Thanks @jmsosso!
drush sql:drop and drush sql:query ... working good for Drush 9.x and Drupal 8.8.x!

Again since this is the first thing that comes up in google:
drush sql-drop -y
drush sqlc < sites/default/files/yourbackup.sql

https://www.drush.org/latest/commands/sql_query/
Trying both way sql:query and sqlc and result is a non found file. :/
So, how to target the sql file when it's in the drupal root, same level as site folder ?

Drupal root    : /var/www/drupal-8
Drush version  : 9.7.1

Commands would be:
drush @lias sql:drop && drush @lias sql-query --file=../exports-bruts/file.mysql.gz or
drush @lias sql:drop && drush @lias sqlc < /exports-bruts/file.mysql.gz

@aiphes keep in mind that for drush the current directory is the Drupal root. So if the dump is in the Drupal root, no matter from which directory you run drush, the command will be drush sql-query --file=file.mysql.gz

If you what to use relative paths, like ../exports-bruts/file.mysql.gz that means that exports-bruts should be one level up from the Drupal root. If you are not sure, the better is to use full paths, like /home/user/project/drupal/html/xxx.sql :)

@jmsosso
I done what I suggest and I get the same error:
dump is stored in : /var/www/drupal-8/exports-bruts/bdd.mysql

and when I run :

drush @lias sql:drop && drush @lias sql-query --file=bdd.mysql.gz

 Do you really want to drop all tables in the database d8bl? (yes/no) [yes]:
 >

 [error]  File(s) not found: bdd.mysql.gz
drush @lias sql:drop && drush @lias sql-query --file=/var/www/drupal-8/exports-bruts/bdd.mysql.gz

 Do you really want to drop all tables in the database d8bl? (yes/no) [yes]:
 >

So I need to use the full path to get this work.. randomly.

[error] File(s) not found: /var/www/drupal-8/exports-bruts/bdd.mysql.gz :/

Very weird thing.