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.
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.
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.
We’ll give it a name and specify the appropriate SQL version. Then, click create.
We are returned to the Options Group list. With our newly created options group selected, select Add Option.
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.
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.
Now that the options group is created, we’ll need to modify our database instance to use the newly created options group.
Toggle the options group to your newly created options group. Then, check the apply immediately option and click Continue to modify the DB instance.
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.
Executing the Backup
The backup is executed via a stored procedured named rds_backup_database.
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;
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’
Once you’ve got a SUCCESS, check your S3 bucket and you should see you backup created there.
That’s it!
