Backing Up and Restoring Native SQL Server Backups in AWS RDS

We've recently been moving all our infrastructure over to AWS at Mercury. This has come with many benefits such as finer control over security and easier maintenance of our infrastructure

We've recently been moving all our infrastructure over to AWS at Mercury.  This has come with many benefits such as finer control over security and easier maintenance of our infrastructure.  Part of this migration involved moving our SQL Server databases from EC2 instances to AWS RDS (Relational Database Service) which came with a interesting set of challenges, but none were showstoppers.

This article addresses one of the challenges we faced: being able to make and restore native SQL Server backups when using AWS RDS. AWS has documented this (see Sources below) but I have written my own documentation for easier reference. I hope you find this useful as well.

AWS Setup

Before we can make backups and restore native backups in AWS RDS, we need to set up our AWS infrastructure. This involves setting up a AWS S3 bucket to store the backups and then giving our AWS RDS instance access to the AWS S3 bucket to backup and restore databases to and from the bucket.  

AWS S3 Bucket Setup

The first thing we need to do is create an AWS S3 bucket that can be used to save our native database backups to.  We will also be able to restore backups from this bucket.

  1. Navigate to S3 in the AWS Console:
    S3 in AWS Console
  2. Click on Create bucket to create a new bucket:
    Create bucket butten
  3. Give your bucket a unique name and select the desired region for your bucket:
    S3 Bucket Name and region page
  4. Since we do not need to give public access to the bucket or configure anything else, click Create.

That's all you need to do to create the S3 bucket.  Take note of the name you gave the bucket and then you can move on to the next section.

AWS RDS Setup

Now that we have created our S3 Bucket, we can configure our AWS RDS instance to access that bucket. We do this by creating an Option Group that has native backups enabled and then associating that Option Group with our AWS RDS Instance.

Option Group Setup

Let's first create the Option Group and give it the correct permissions to access the S3 bucket.

  1. Navigate to RDS in the AWS Console:
    Selection_024
  2. In the sidebar, click on Option groups and then Create group:
    Selection_025
  3. Give the Option Group a descriptive Name and Description. Then make sure that the Engine and Major engine version match that of the AWS RDS Instance you are going to add this Option Group to! Once you have completed this, click Create
    Selection_026
  4. In the Option groups table, select the new Option Group we just created and click Add option:
    Selection_027
  5. Populate the form as follows and then click Add option:
    • Option details
      • Option name: SQLSERVER_BACKUP_RESTORE
    • IAM role
      • IAM role: Create a new role
      • IAM role name: Descriptive name for new IAM role
    • S3 destination
      • S3 bucket: Bucket you created earler
    • Scheduling: Immediately

Associate Option Group with RDS Instance

Now that we have created the Option Group, all that's left to do is associate the Option Group with our RDS Instance.

  1. In the sidebar, select Databases:
    Selection_030
  2. Select your RDS Instance and click Modify:
    Selection_029
  3. Scroll down to the Database options section and change the Option group field to your new Option Group you just created:
    Selection_031
  4. Click Continue
  5. In the Schedule of modifications section, select Apply immediately
    Selection_032
  6. Click Modify DB Instance
  7. Wait for the changes to be applied and then you are finished! See below for commands on how to execute backups and restores.

Executing Backup and Restores

Now that we have set up our AWS Infrastructure, we can use the following commands to execute backup and restore tasks on our AWS RDS instance.

Backing Up

We can backup both unencrypted and encrypted databases.

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;
  • @source_db_name: The name of the database to backup
  • @s3_arn_to_backup_to: The name of the bucket and filename and to use for the backup
  • @overwrite_S3_backup_file: Specifies if the new backup should automatically overwrite any older backups with the same file name in the bucket. 1 = true, 0 = false

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;
  • @source_db_name: The name of the database to backup
  • @s3_arn_to_backup_to: The name of the bucket and filename and to use for the backup
  • @kms_master_key_arn: The ARN for your KMS Master Key used to encrypt the database
  • @overwrite_S3_backup_file: Specifies if the new backup should automatically overwrite any older backups with the same file name in the bucket. 1 = true, 0 = false

Restoring

We can restore both encrypted and unencrypted databases to our RDS Instance.

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_db_name: The name of the database to restore the backup to
  • @s3_arn_to_restore_from: The name of the bucket and filename of the backup file that should be restored

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';
  • @restore_db_name: The name of the database to restore the backup to
  • @s3_arn_to_restore_from: The name of the bucket and filename of the backup file that should be restored
  • @kms_master_key_arn: The ARN for your KMS Master Key that is used to encrypt the database

View Backup and Restore Progress

Once you have executed a backup or restore, the process runs in the background.  To view the actual status and progress of a backup or restore, you can use the following commands:

View all backup/restore tasks

This will return all the past and current backup/restore tasks.

exec msdb.dbo.rds_task_status

View all backup/restore tasks for a database

This will return all the past and current backup/restore tasks for the specified database.

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

View backup/restore task with a specified ID

This will only return the backup/restore task with the corresponding ID.

exec msdb..rds_task_status @task_id= 5

Sources

https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/