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.

The official Amazon documentation describes how to Import Data Into SQLServer on Amazon RDS using a convoluted and lengthy procedure.

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

1. 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.
2. Zip your .bak file and upload it to this new instance
3. To transfer the file securely, you can copy/paste it via remote desktop.
4. Another way is to install FileZilla FTP Server
and set up FTP over SSL. Then, use WinSCP FTP client software to upload the file there (with resume capability). Both of these are freeware.
5. Restore the .bak file into the SQL server database local to that Amazon EC2 instance
6. Install and run the SQL Azure Migration tool.
7. Select the source database as the local server
8. Select Analyze/Migrate > SQL Database
9. Generate scripts (Edit the .xml config file in the SQL Azure folder if you need to change the location where the scripts are generated. They can get large!)
10. Select the destination server as "SQL Server" type and enter the RDS hostname / endpoint.
11. Run the scripts on the destination server
12. Save the RTF log files for troubleshooting

The above procedure is a quick and painless way to retore your SQL Server .bak backup files into an Amazon RDS instance.

References

AWS RDS SQL Server vs. SQL Azure Smackdown – Importing Data - a short video showing how to use the Migration tool to import or load data into SQL Server on Amazon RDS.