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

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

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.

You can create a backup locally from AWS RDS. Using SQL Management Studio, right-click your database > Task > Export Data

You just have to choose the right "Data Source", e.g. "SQL Server Native 11" for both, the source and destination.

It is well described in AWS documentation page: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Exporting.SSIEW

Section: "SQL Server Import and Export Wizard"