I'm using the following command::
drush sql-sync @prod @dev
As I experienced, it does not drop tables on target (@dev) machine.
Is there a way, while syncing this way, to drop tables on target, so databases match completely? Or I must manually first drop tables on target?
Drush sql-sync will not drop tables on the target unless you specify --create-db. Note that the later drops and recreates the entire database; if you do not have permissions to do this, you could run drush sql-drop on the target first.
There has been a feature request to add this functionality (drop before sync) in the Drush issue queue for years: http://drupal.org/node/716412. Patches welcome.
Important update: On Drupal 7, if you drop tables on the target prior to sql-sync, you could run into trouble unless you have http://drupal.org/node/1996004#comment-7419764. This is fixed in 8.x-6.x-dev, but is not part of 5.9, 7.x-5.x-dev or 8.x-6.x-beta1.
Update 2: This fix has now also been committed to 7.x-5.x-dev.
Here is the bash script that deletes all tables that are exist on @live, but do not exist on local:
drush sqlq "DROP TABLE $(comm -13 <(drush @live sqlq 'SHOW TABLES') <(drush sqlq 'SHOW TABLES') | paste -sd ',' - | sed 's/,/, /g');"
It assumes that you execute it after updating local database from @live, so after:
drush sql-sync @live default
It is helpful if you have no permissions to use --create-db.
This works on Drush 8 with Drupal 7:
drush @dev sql-drop -y && drush sql-sync @prod @dev
Careful, this will drop all tables on @dev without prompting (the -y flag).
