Migrate an on-premises Microsoft SQL Server database to Amazon RDS via Amazon S3

This migration process includes making a backup and restoring the backup in an Amazon Simple Storage Service (Amazon S3) bucket and using SQL Server Management Studio (SSMS).

This process includes following major steps:

1. Making a backup of on-premises database via SSMS (SQL Server Management Studio)
2. Transferring that backup file in Amazon Simple Storage Service (Amazon S3)
3. Finally, restoring the backup file in Amazon RDS for SQL Server.

Create an Amazon RDS for Microsoft SQL Server DB instance

• Select SQL Server as the database engine in Amazon RDS for SQL Server.
• Choose the required SQL Server Edition

Create a backup file from the on-premises Microsoft SQL Server database

• Connect to the on-premises SQL Server database through SSMS.
• Create a backup of the database.

Upload the backup file to Amazon S3

• Create a bucket in Amazon S3.
• Upload the backup file to the S3 bucket.

Restore the database in Amazon RDS for SQL Server

• Open the Amazon RDS console
• Choose Option groups in the navigation pane.
• Choose the Create group button.
• Add the SQLSERVER_BACKUP_RESTORE option to the option group
• Add the option group to Amazon RDS for SQL Server.
• Connect to Amazon RDS for SQL Server through SSMS.
• Call the rds_restore_database stored procedure to restore the database.

More details on Restoring a database

As mentioned above for restoring the database in RDS call the rds_restore_database stored procedure. Amazon RDS creates an initial snapshot of the database after the restore task is complete and the database is open.

exec msdb.dbo.rds_restore_database
@restore_db_name='database_name',
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension',
@with_norecovery=0|1,
[@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id'],
[@type='DIFFERENTIAL|FULL'];

Example of single file restore:

exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup1.bak';

Example of multifile restore:

exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup*';

Check restoring task status:

exec msdb.dbo.rds_task_status
[@db_name='database_name'],
[@task_id=ID_number];

Example:
exec msdb.dbo.rds_task_status @db_name='mydatabase';

Source: AWS Documentation