Restore db from backup in SSMS - Amazon RDS SQL Server Error 229

3
votes

I have just signed up for Amazon Web Services for hosting my ASP.NET MVC 5 app. My app uses 3 databases, 1 from database first approach and 2 from code first approach. For that matter I have created databases from SSMS by connecting to the given server name for RDS and master UserName and Password. But now I want to restore my database first database from my local machine. And when I tried to do so, the following error occured when I pressed add backup from device:

EXECUTE permission

I have tried giving the EXECUTE permission in that database properties but of no use. How can I restore it?

1
sql-serveramazon-web-servicesamazon-rds

1 Answers

3
votes

You can't use the backup and restore when using RDS to transfer data into RDS. You'll need to use another means to get the data in there.

If you have an existing Microsoft SQL Server deployment that you want to move to Amazon RDS, the complexity of your task depends on the size of your database and the types of database objects that you are transferring. For example, a database that contains data sets on the order of gigabytes, along with stored procedures and triggers, is going to be more complicated than a simple database with only a few megabytes of test data and no triggers or stored procedures.

RDS for SQL Server service does not currently support RESTORE DATABASE ... FROM FILE, because the database and log file backups must be local to the SQL Server instance. Similarly, FILESTREAM is also not supported at this time.

The BULK INSERT and OPENROWSET(BULK...) statements from the server are not supported import procedures due to their dependency on the ADMINISTER BULK OPERATIONS permission which is not granted for SQL Server DB instances. Please use the process outlined below to import data to a SQL Server DB instance.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html