Amazon-rds – Import data into AWS RDS sql server through SQL Server management studio

amazon-rdsaws

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.

Best Answer

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

Related Question