Download Amazon-RDS (SQL-Server) Backup or Snapshot

This is super easy to do if you follow the steps Importing and Exporting SQL Server Databases

Key steps being:

  • Create S3 bucket
  • Create options group with IAM Role to that bucket, being sure to select the "SQL_SERVER_BACKUP_RESTORE" option.
  • Assign options group to the RDS instance
    • Go to RDS dashboard showing your instances.
    • Click your RDS instance to view the details screen.
    • Click the Modify button in the top right.
    • Scroll way down to "Database options" -> "Options group"
    • Select your newly created option group that has the backup & restore option.
    • Save / Continue. It may take several minutes for this change to take effect, even if you select to apply it immediately. I personally did not experience downtime, YMMV.
  • Run the backup script:
exec msdb.dbo.rds_backup_database 
     @source_db_name='database_name', 
     @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
     @overwrite_S3_backup_file=1;

That script will return to you a task #. You can use the following script to track the progress:

exec msdb..rds_task_status @task_id= <your task #>

Once you've done that then you can download the .bak file and restore locally (assuming same sql server versions running, etc)