Can I make Drush use a pattern to name sql-sync dump files? I'm using Drush to sync up my dev, staging and live sites using rsync and sql-sync. I want sql-sync to create a dump file named using a pattern. For example, I want the dump file to be named site_database_name__date_time. Looking in example.drushrc.php I saw you can use @DATABASE and @DATE for the --result-file option in sql-dump. I tried using the same pattern in my site alias definition but it didn't quite work. The dump file is created properly, using the replacement pattern. But when it tries to restore the database to the target site it throws an error saying there's no such file as "@DATABASE_@DATE.sql". So it's not making the replacement when it does the restore phase of sql-sync. Is there a way to get this to work? Or am I taking the wrong approach here? 

I realize the sql-sync documentation references a "temp" directory for these dump files, implying they aren't meant to be kept around. My thought is to keep them around for a record of what was used for each sync in case something goes wrong.

Below is an example of the site alias I'm using, which specifies the %dump path alias used by sql-sync.

$aliases['dev'] = array( 
  'uri' =&gt; 'dev.example.com', 
  'root' =&gt; '/home/websites/dev.example.com', 
  'path-aliases' =&gt; array( 
    '%files' =&gt; 'sites/files', 
    '%dump' =&gt; '/home/username/tmp/drush/@DATABASE_@DATE', 
  ), 
  'command-specific' =&gt; array ( 
    'sql-sync' =&gt; array ( 
      'simulate' =&gt; '1', 
      'structure-tables' =&gt; array( 
        'custom' =&gt; array('cache', 'cache_filter', 'cache_menu', 
          'cache_page', 'history', 'sessions', 'watchdog'), 
      ), 
    ), 
  ), 
);
If you use the '%dump-dir' directive, then drush will create a name for your dump file based on the name of the remote host and the name of the database.  It is best to not include the date in the database name for remote sql-syncs, because the rsync operation will be faster if it can update only the incremental changes in the database dump, and this of course would not be possible if the filename changed on every operation.

If you want the date to appear in the database dump's filename so that it can also serve as a backup of your drupal site, you must also be careful to back up the files at the Drupal root as well.  It is common for Drupal to simply fail (with errors or even a WSOD) if you try to use an sql database with a different version of the code (core or contrib) than was used to create the database.

An easy way to backup your database and files together is with the drush archive-dump command, which also has a convenient archive-restore command to go the other way.

Some combination of the above is probably a better solution than what you are trying to do in your question above.  The exact feature you are looking for is not currently supported in drush.  We could certainly consider feature requests to add that capability (ideally posted to the drush issue queue with a proposed patch already attached -- see function drush_sql_dump_file in commands/sql/sql.drush.inc), but there may be some reluctance to accept it, since the alternatives already described are probably better in most instances.