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
- Under RDS Dashboard create a new option group with "SQLSERVER_BACKUP_RESTORE" option.
- Update your RDS instance to use the newly created option.
- 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: