On-premises SQL Server to Amazon RDS with native backup and restore from S3

#1. Set up the RDS side

1. S3 bucket in the same region as the RDS instance.
2. IAM role RDS can assume, with s3:ListBucket, s3:GetBucketLocation, s3:GetObject, s3:PutObject (and kms:Decrypt if encrypted with KMS).
3. Option group with the SQLSERVER_BACKUP_RESTORE option pointing to that role; attach it to the instance.

aws rds create-option-group --option-group-name sql-backup-restore \
  --engine-name sqlserver-se --major-engine-version 16.00 --option-group-description "native backup"
aws rds add-option-to-option-group --option-group-name sql-backup-restore --apply-immediately \
  --options "OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::111122223333:role/rds-sql-backup}]"
aws rds modify-db-instance --db-instance-identifier rds-sql-prod --option-group-name sql-backup-restore --apply-immediately

#2. Back up on-premises and upload

BACKUP DATABASE Sales TO DISK = 'D:\mig\Sales_full.bak' WITH COMPRESSION, CHECKSUM;

aws s3 cp D:\mig\Sales_full.bak s3://sql-mig-bucket/sales/ --storage-class STANDARD

For large files use multipart (automatic in the CLI) and consider striped backups (multiple files restored with a wildcard *).

#3. Restore with NORECOVERY (minimal downtime pattern)

EXEC msdb.dbo.rds_restore_database
  @restore_db_name = 'Sales',
  @s3_arn_to_restore_from = 'arn:aws:s3:::sql-mig-bucket/sales/Sales_full.bak',
  @with_norecovery = 1;

EXEC msdb.dbo.rds_task_status @db_name = 'Sales';   -- watch progress

Then apply differentials and logs as they are copied:

EXEC msdb.dbo.rds_restore_database @restore_db_name = 'Sales',
  @s3_arn_to_restore_from = 'arn:aws:s3:::sql-mig-bucket/sales/Sales_diff.bak',
  @type = 'DIFFERENTIAL', @with_norecovery = 1;

EXEC msdb.dbo.rds_restore_log @restore_db_name = 'Sales',
  @arn_to_restore_from = 'arn:aws:s3:::sql-mig-bucket/sales/Sales_log_0001.trn',
  @with_norecovery = 1;

#4. Cutover

1. Stop the app; take the final log backup (WITH NORECOVERY on source to freeze it).
2. Upload and restore it with @with_norecovery = 1.
3. Bring the database online:

EXEC msdb.dbo.rds_finish_restore @db_name = 'Sales';

4. Recreate logins and fix user mappings; recreate Agent jobs; switch connection strings.

#5. Limits to know

• RDS restores one task at a time per instance by default (tasks queue).
• No sysadmin on RDS: anything in the backup needing it (server-level objects) must be recreated with the master user.
• TDE-encrypted backups need the certificate imported via the RDS TDE procedures first (see RDS docs for rds_restore_tde_certificate), and the TDE option.
• Database size limit per RDS instance storage (up to 64 TiB) — check your target.
• Multi-AZ: restoring into a Multi-AZ instance works; for large restores some teams restore Single-AZ, then convert to Multi-AZ.

Tip

Script the whole chain (upload → restore → status polling) with PowerShell and run it in a rehearsal. On cutover night you just run the final-log part.