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.
- Another way is to install FileZilla FTP Server
- Restore the .bak file into the SQL server database local to that Amazon EC2 instance
- Install and run the SQL Azure Migration tool.
- Select the source database as the local server
- Select Analyze/Migrate > SQL Database
- 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!)
- Select the destination server as “SQL Server” type and enter the RDS hostname / endpoint.
- Run the scripts on the destination server
- Save the RTF log files for troubleshooting
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.
The above procedure is a quick and painless way to retore your SQL Server .bak backup files into an Amazon RDS instance.