sql-server limitations Migrate to Amazon SQL Server RDS



restore sql server database to aws rds (5)

I have been attempting to move from a regular SQL Server on a Win2008 Server to the SQL Server on Amazon AWS RDS.

I thought an simple backup and restore would work. Though AWS RDS doesn't seem to have access to a file system so the sql scripts all seem to need a local file system on the source and destination server. I attempted a script following

exec sp_addlinkedserver @server='test.xxxx.us-east-1.rds.amazonaws.com'

-- Verify that the servers were linked (lists linked servers)
exec sp_linkedservers

EXEC ('RESTORE DATABASE [orchard] FROM DISK = ''C:\Temp\orchard.bak'' WITH FILE = 1,  NOUNLOAD, STATS = 10')
AT [test.xxxx.us-east-1.rds.amazonaws.com]

Any Suggestions would be helpful.



Use the export wizard in sql server management studio on your source database. Right click on the database > tasks > export data. There is a wizard that walks you through sending the whole database to a remote sql server.


download the free 'SQL Azure Migration Wizard' from CodePlex -- I did a short blog/screencast about this. Be sure to set the 'TO' setting in the wizard to the AWS DNS name and then use 'SQL Server 2008' and not 'SQL Azure'


The official word I got for AWS support on migration of SQL databases using .bak files is that it is not supported. So no more quick restore from .bak files. They offered the official help for migration of existing databases here:

Official AWS database migration guide

And the also gave me an unofficial wink at the Azure database migration tool. Just use it to generate a script of your schema and or data and execute it against your RDS instance. Its a good tool. You will have to import the .bak into a non-RDS SQL server first to do this.

SQL Azure migration tool


I wrote up some step-by-step instructions on how to restore a .bak file to RDS using the SQL Azure Migration Tool based on Lynn's screencast. This is a much simpler method than the official instructions, and it worked well for several databases I migrated.





amazon-rds