How To Restore a SQL Server BAK file to Amazon RDS

This article describes how to restore a SQL Server database backup file (.bak) to a SQL Server database hosted on Amazon's Relation Database Service (RDS).

Amazon RDS for SQL Server does not allow you to restore a .bak file (as of Apr. 2013). If you attempt to use the Restore Database... function and select a File (Device) to be restored, you will get an error saying Failed to retrieve data for this request. Amazon has disabled this function.

To solve the issue, you can use the free SQL Azure Migration Wizard (available from CodePlex). This high-quality tool is by Amazon RDS's cloud database competitor, Microsoft SQL Azure, and it allows you to migrate databases between servers.

Steps to Restore .bak SQL Server backup file to Amazon RDS

Launch a Windows + SQL Server Amazon EC2 on-demand instance. (Use the same availability zone as your Amazon RDS instance to speed up the data transfers.) Make sure to increase the default size of the EBS volume to accomodate 3-4 times the space occupied by the .bak file. You'll need space to store the .bak file, the restored database MDF/LDF, and the scripts generated for the migration. You can also create a separate EBS volume.

Zip your .bak file and upload it to this new instance

To transfer the file securely, you can copy/paste it via remote desktop.