RDS/SQL Server Backup and Restore to/from S3

NOTE: the purpose of this resource is to facilitate seamless initial replication/seeding when the CLOUDBASIC semi-automatic backup-restore method of replication is employed. CLOUDBASIC handles SQL Server zone-to-zone (Multi-AZ with readable-replicas) and cross-region (Geo-Replicate) replication continuously, achieving a near real-time replication with potential data loss in the seconds for DR scenarios. It does not rely on snapshotting via S3, which would result into substantially larger potential data loss.

Backup database to S3

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

Restore database from S3 .bak file

exec msdb.dbo.rds_restore_database 
    @restore_db_name='database_name', 
    @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/folder/file_name_and_extension';

More information, including how to export an encrypted backup and restore a database from an encrypted S3 backup file, visit:

http://docs.aws.amazon.com/AmazonRDS/...