How to backup Amazon RDS MS SQL Server database instance and restore locally

Question

It is possible to create a backup of a database running on an Amazon RDS instance and restore it on a local machine using the standard Task -> Backup and Task -> Restore features within Microsoft SQL Server Management Studio? If so, how do you go about doing this?

Note, this question does not pertain to whether you can bulk copy the data or generate the scripts, but whether you can create a true .BAK database backup which can be restored using the SSMS restore feature.

Answer 1

You cant currently create a .bak file out of amazon rds. The way I handle this is to use the azure migration wizard which just happens to also work with amazon rds.

I spin up a EC2 instance with SQL Server and the migration wizard installed. I then use the migration tool to copy the RDS database to the ec2 instance.

once that is done you can create a .bak file from the SQL Server running on the EC2 instance. its a pain but it works. if you have the bandwidth or your database is small you may be able to use the migration tool directly on your target machine.

http://sqlazuremw.codeplex.com

Answer 2

You can now get a bak file out into S3. Here are the instructions: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Here's a quick snippet that'll generate a backup:

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;

Before this feature, I could get a bacpac out that worked well in my case. In SSMS, right-click the database > Tasks > Export Data-tier Application.

I was able to import this onto my server without any issues.