Backing up an AWS RDS MSSQL Database to a S3 Bucket

AWS’ managed relational database service, RDS, can be a good option for many deployments.

One thing you’ll find that different when working with RDS is that you won’t find the normal Tasks > Back Up… option available for a given database.  This is because the service doesn’t allow direct disk access to the instance(s) running you database.

Perhaps you want to generate a backup (.bak) for testing elsewhere.  You can do this via S3, but it does take some configuration.

For our scenario, we’ve got a single-AZ db.t2.micro RDS instance with SQL Server 2016 Express.

db2_001.png

Create a S3 Bucket

First, we’ll create an S3 bucket in the same region as your RDS instance.  No special permissions need to be defined at this time.

rds21.png

Modify your RDS Instance 

Next, we have to add the SQLSERVER_BACKUP_RESTORE option needs to be added to the RDS instance.

My instance is currently using the default options group. You can’t add options to a default options group. So, I will create a new one.

rds1_001.png

We’ll give it a name and specify the appropriate SQL version. Then, click create.

rds11.png

We are returned to the Options Group list. With our newly created options group selected, select Add Option.

rds12.png

We’ll specify the SQLSERVER_BACKUP_RESTORE option. Then we’ll create a new IAM Role. This process is will give SQL the permissions to backup to the S3 bucket. Unless you already have a role configured for this, click Create a New Role.

rds5.png

It’s necessary to set an IAM Role name and specify the S3 bucket we created at the beginning of this process.  Since I am intending for the backups to go directly into the bucket (no subfolders), I’ll leave the S3 Bucket Prefix blank.

rds6.png

Now that the options group is created, we’ll need to modify our database instance to use the newly created options group.

rds8.png

Toggle the options group to your newly created options group. Then, check the apply immediately option and click Continue to modify the DB instance.

rds12_001.png

This takes a few minutes to apply and can be monitored in the Configuration Details of your RDS instance. Once you see the new options group “in-sync” you’re ok to proceed.

rds15.png

Executing the Backup

The backup is executed via a stored procedured named rds_backup_database.

rds16.png

To kick off the stored procedure, you’ll need to execute the following:

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;

rds18.png

This process takes a bit to complete, even on the smallest of databases. You can check on the status with the following query:

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

rds19.png

Once you’ve got a SUCCESS, check your S3 bucket and you should see you backup created there.

rds20.png

That’s it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s