How do I backup / restore an RDS database instance on AWS?

How do I export/import .BAK files from S3 to an RDS instance?

Answer

Backup

To backup a database, connect to the server and execute the following SQL (note this will overwrite any existing backup):

exec msdb.dbo.rds_backup_database
@source_db_name='DATABASE_NAME',
@s3_arn_to_backup_to='arn:aws:s3:::aswbucketname/DATABASE_NAME.bak',
@overwrite_s3_backup_file=1

This operation will return a task ID. Backing up a database is an asynchronous operation, so you'll need to check on the status using the returned task ID:

exec msdb.dbo.rds_task_status @task_id=5;

Restore

To restore a database:

exec msdb.dbo.rds_restore_database
@restore_db_name='DATABASE_NAME',
@s3_arn_to_restore_from='arn:aws:s3:::aswbucketname/DATABASE_NAME.bak';

This operation is also asynchronous and will also return a task ID, which can be passed to rds_task_status to check on the status.

These operations are done when the lifecycle value is "COMPLETE". Other possible lifecycle values are here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Tracking.Response