Import data into AWS RDS sql server through SQL Server management studio

Question

I have created an RDS instance running SQL Server SE, this does not allow remote desktop access, so I also have an EC2 instance running Windows Server 2012 base, and I have installed SQL Server Management Studio and I'm able to connect and login using the master user and password.

I want to restore a database .bak file. So I used the SQL Server management studio and chose the option Databases -> ‘Restore Database'. I chose ‘File’ as the backup media and then click ‘Add’. When I click on Add, it throws the below error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.

I looked into the internet for answers and some one suggested to grant sysadmin role to the master user. When I try to do that, I get an error saying "The user does not exist or you do not have permissions to do so"

I have run out of ideas on how to resolve the issue. Any help is greatly appreciated.

Answer 1

There are stored procedures that you can do on Rds

EXEC rds_backup_database 'DBName','.bak file path','','0'

EXEC rds_restore_database 'DBName','.bak file path',''

File path use S3.

Just to add more context for the op.

The RDS instance doesn't give you access to the underlying file system like an ec2 would, hence you have to use s3 buckets.

See here for full details:- http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Answer 2

This issue is caused due to AWS RDS permissions.

While restoring, when we select restore from a file, Microsoft SSMS searches at the physical location where the database is running. But AWS RDS is designed in such a way that we don't get file-level access to the RDS instance. Instead, we get an endpoint and port to connect it.

See Importing and Exporting SQL Server Databases in the Amazon Relational Database Service (RDS) User Guide for more details.