6

I want to download one of my backups and restore it on my laptop for testing a web app locally. I'm using SQL Express.

This is similar to this MySQL question. Can I just run a backup using T-SQL? And then somehow direct it to S3?

7

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)

  • Thanks, I will when I can, and come back and mark answer. So the above is to be run in SSMS? Not Windows command line, right? They made their own backup stored proc? – NealWalters Sep 22 '17 at 13:25
  • Yes, that is correct – brodie Oct 24 '17 at 0:35
  • 23
    That is not super easy. Super easy would be clicking a download button somewhere. – shredding Jun 28 '18 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.