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

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/

I had the same issue and AWS has Microsoft SQL Server Native Backup and Restore Support for similar senarios.

Go through the below links you will get the entire details.. https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

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

To export data from MS SQL Server on Amazon RDS, you can use either of these choices:

1. Backup of Native Database using .bak file

Amazon RDS supports native backup for SQL Server databases using the .bak file. Create a full backup of on-premises databases and store it on Amazon S3. Now, restore the backup file onto Amazon RDS DB instance running SQL Server.

2. SQL Server Import and Export Wizard

Navigate through the Object Explorer to get to the Wizard. When choosing a data source select your RDS SQL Server instance. Use the master user credentials in the Username/Password fields. In destination select SQL Server Native Client 11.0. The wizard can be used for both backups and exports.

This is not currently supported using Amazon RDS SQL Server. You will have to actually dump the data from the database instance, and cannot produce a .bak file.

I would suggest checking out the SQL Database Migration Wizard. It was built to support SQL -> Azure, but will also allow you to go SQL -> SQL. You will be able to export the data from RDS and import into another database using this tool.

An example of using the tool in this manner. The article is written for importing into RDS, but you can export from RDS in a similar fashion.