How do I perform a native backup of my SQL Server database to Amazon RDS and restore from Amazon S3?

Resolution

Before you begin the native backup, make sure that you set up for native backup and restore.

Note: The Amazon S3 bucket must be in the same AWS Region as your Amazon RDS DB instance.

Set up a native backup of the SQL Server database

Complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Option groups.
3. Choose Create group, and then enter the name, description, engine, and engine version of your server.
4. Choose Create.
5. Select the option group, and then choose Add option.
6. For Option name, choose SQLSERVER_BACKUP_RESTORE.
   Note: It's a best practice to create a new AWS Identity and Access Management (IAM) role so that the role has the required permissions.
7. For S3 destination, select your S3 bucket.
8. For Scheduling, choose Immediately.
9. Choose Add option.

Associate the option group with the DB instance

Complete the following steps:

1. In the navigation pane, choose Databases, and then select the instance to back up.
2. Choose Modify, and then choose Additional configuration.
3. For Database options, choose your option group, and then choose Continue.
4. Review the Summary of modifications.
   Note: There's no downtime because you don't need to reboot the instance to modify this option group.
5. For Schedule modifications, choose Apply immediately, and then choose Modify DB Instance.
6. Wait for the status to change from Modifying to Available.

Connect to the DB instance through SSMS

Complete the following steps:

1. Log in to Microsoft SQL Server Management Studio (SSMS) as the db_owner or db_backupoperator.
2. Choose New Query, and then initiate the backup of your database.
3. Enter one of the following SQL statements.
   Initiate backup for unencrypted databases:

   exec msdb.dbo.rds_backup_database @source_db_name='database_name', @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',@overwrite_S3_backup_file=1;

   Initiate backup for encrypted databases:

   exec msdb.dbo.rds_backup_database @source_db_name='database_name',@s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',    
   @kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id',  
   @overwrite_S3_backup_file=1;

   Note: In the preceding statements, replace database_name, bucket_name, file_name_and_extension, region, account-id, and key-id with your values. To restore the user database to a new Amazon RDS DB instance, use the backup file that you generated in the S3 bucket. When you call the rds_backup_database or rds_restore_database procedure, the task produces the information about the task.

When the lifecycle status of the task shows SUCCESS, the task is complete. Use the Amazon S3 console to view the backup file in the bucket where you created the user database backup. To restore the DB instance, download the backup file that's in the S3 bucket or use the user database backup file. You can either restore to the same Amazon RDS for SQL Server DB instance or to a new Amazon RDS DB instance. You can also restore this backup file to an on-premises server or to a different Amazon RDS DB instance that runs SQL Server. For more information, see Restore a backup on the Microsoft website.

To restore from the backup file that's in the S3 bucket, use one of the following SQL statements.

Restore unencrypted databases:

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

Restore encrypted databases:

exec msdb.dbo.rds_restore_database @restore_db_name='database_name',@s3_arn_to_restore_from='arn:aws:s3::: bucket_name/file_name_and_extension',  
@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id';

Note: In the preceding statements, replace database_name, bucket_name, file_name_and_extension, region, account-id, and key-id with your values.

After you run the backup or restore statement, you can get the Task ID. Or, you can use the following script to identify all completed and pending tasks for a specific database:

exec msdb.dbo.rds_task_status @db_name='database_name'

Note: Replace database_name with the name of your database.

To track the status of the job, use the following SQL statement:

exec msdbo.rds_task_status @task_id= 5

For information about errors that you might encounter and their solutions, see Migrating Microsoft SQL Server Enterprise workloads to Amazon RDS.

Related information

Introduction to backups

Backing up, restoring, and exporting data

Importing and exporting SQL Server databases using native backup and restore