How to Export/Backup DB from SQL Server on Amazon RDS

Question

I have a SQL Server database on Amazon RDS. How can I export or Backup the database? when I try to I get an 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'.

What I'm basically trying to do is to export the database and then import it on to Amazon EC2 on EBS.

Haven't been able to find solution for past 2 or 3 days.

Please help!!!! :)

Answer 1

As of July, this can be achieved by the following:

1. Under RDS Dashboard create a new option group with "SQLSERVER_BACKUP_RESTORE" option.

2. Update your RDS instance to use the newly created option.

3. Open SQL Management Studio, connect to RDS database and execute the following to kick off the backup:

USE [msdb]
GO

DECLARE   @return_value int

EXEC  @return_value = [dbo].[rds_backup_database]
      @source_db_name = 'your_database_name',
      @S3_arn_to_backup_to = 'arn:aws:s3:::your-bucket-name/folder/db.bak',
      @KMS_master_key_arn = NULL,
      @overwrite_S3_backup_file = NULL

SELECT    'Return Value' = @return_value

GO

To check the progress of the backup, run the following:

> USE [msdb] GO
> 
> DECLARE   @return_value int
> 
> EXEC  @return_value = [dbo].[rds_task_status]         @db_name =
> 'your_database_name',         @task_id = <<<found in result of previous query>>>
> 
> SELECT    'Return Value' = @return_value
> 
> GO

More information here: https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/