How do I perform native backups of an Amazon RDS DB instance that runs SQL Server?

Short description

With Amazon RDS backups, you can store the backup file in Amazon Simple Storage Service (Amazon S3). You can also use the database backup file to restore to the same or a different Amazon RDS for SQL Server DB instance.

Amazon RDS supports native backup and restore for Microsoft SQL Server databases. You can create a full backup of your on-premises database and store the file in Amazon S3. You can then restore the backup file to an existing Amazon RDS DB instance that runs SQL Server. 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, Restore a backup on the Microsoft website.

Resolution

Before you begin the native backup, make sure that you have the following components:

• An Amazon S3 bucket to store your backup files
  Note: Create the S3 bucket in the same Region as your RDS DB instance.
• An AWS Identity and Access Management (IAM) role to access the bucket
• The SQLSERVER_BACKUP_RESTORE option added to an option group on the DB instance

To set up a native backup of the SQL Server database, follow these steps:

1. Open the Amazon RDS console, and then choose Option Groups in the navigation pane.
2. Choose Create Group, and enter the name, description, engine, and engine version of your server.
3. Choose Create.
4. Select the option group that you created, and then choose Add Option.
5. Choose SQLSERVER_BACKUP_RESTORE. It's a best practice to create a new IAM role and then choose Add Option so that your IAM role has the required privileges.
6. Choose your S3 bucket, or create a new S3 bucket.
7. Choose Apply Immediately and Add Option.
8. To associate the option group with the DB instance, choose Databases in the navigation pane, and then choose the instance to back up.
9. Choose Actions, and then choose Modify.
10. Under Database Options, choose the option group that you created, and then choose Apply Immediately and Continue.
11. Review the information, and then choose Modify DB Instance. This option group modification has no downtime because instance reboot isn't required.
12. Wait for the status to change from modifying to available. Then, to connect to the DB instance through SQL Server Management Studio, use the primary user of your RDS instance.
13. Choose New Query, and then, to initiate the backup of the desired database, 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: Replace database_name, bucket_name, file_name_and_extension, region, account-id, and key-id listed in these examples to match your scenario. To restore the user database to a new RDS DB instance, use the backup file that was generated in the S3 bucket. When the rds_backup_database or rds_restore_database stored procedure is called, the task starts and outputs the information about the task.

When the lifecycle status of the task is SUCCESS, the task is complete. You can then open the Amazon S3 console, choose the bucket where you created the user database backup, and then view the backup file. To restore the DB instance, download this file or use the user database backup file. You can restore the same Amazon RDS for SQL Server DB instance or in a new RDS DB instance.

To restore from the backup file available 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';

You can get the Task ID after you perform the backup or restore statement. Or, you can use the following script to identify all the completed and pending tasks for a particular database:

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

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

exec msdb..rds_task_status @task_id= 5

For a list of potential errors and solutions, see Migrating Microsoft SQL Server Enterprise workloads to Amazon RDS.

Related information

Introductions to backups

Backing up, restoring, and exporting data

Importing and exporting SQL Server databases using native backup and restore