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.
The following article discussing how to Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table is what I needed.
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.
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:
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.
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.