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

sql-server amazon-web-services amazon-rds
Derek Greer picture Derek Greer · Jun 25, 2014 · Viewed 11.3k times · Source

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

M.Scherzer picture M.Scherzer · Jun 25, 2014

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

Related questions

AWS RDS SQL Server unable to drop database

I tried to migrate a SQL Server database by Export Data-tier Application (.bacpac file) from an Amazon RDS instance to other, but import didn't succeed. So now I want to delete the database (which is empty), when I try to: …

Read More
Unable to connect on AWS - RDS DB : SQL Server 2012 Express

I'm new in AWS world. I have an issue to access to my instance of Microsoft SQL Server 2012 Express (11.00.5058) on RDS (US-East-1a). When I have created DB instance, I was able to access to it, create some tables, transfer …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More