SQL Server Backups on AWS RDS

One of the things I love the most about Platform as a Service offerings is the fact that it makes it so I don’t have to do silly things backup SQL Server databases on RDS.

However!

I’m also a paranoid control freak, aka, a DBA. While I appreciate that AWS has a good backup process and I can test it through recovery of my databases, I still want to do my own backups under some circumstances. Can I backup SQL Server databases on RDS?

No and yes. Let’s talk about it.

Backup SQL Server Databases on RDS

I have an RDS SQL Server instance running right now on AWS. I can connect up to it and run the following command:

BACKUP DATABASE HamShackRadio;

Which results in the following:

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database ‘HamShackRadio’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Completion time: 2020-06-26T08:34:23.5511314-04:00

In short, by default, you can’t backup SQL Server databases on RDS. However, that’s by default. We can make some changes.

Really Backup SQL Server Databases on RDS

The setup is a little bit complicated, but not hard. First, if your databases don’t already have an option group, you’ll want to add one. Here’s the CLI I used in PowerShell to add one to my database:

aws rds create-option-group `
    --option-group-name hsroptiongroup `
    --engine-name sqlserver-ex `
    --major-engine-version "14.00" `
    --option-group-description "Option Group to customize SQL Server hsr"

From there, you need to add an option to the option group. The command is pretty explicit:

aws rds add-option-to-option-group `
    --apply-immediately `
    --option-group-name hsroptiongroup `
    --options "OptionName=SQLSERVER_BACKUP_RESTORE, OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::xxxxxxx:role/sqlbackup}]"

Attach the option group to the instance next:

aws rds modify-db-instance `
    --db-instance-identifier hsr `
    --option-group-name hsroptiongroup `
    --apply-immediately

So, done, right? I can now run BACKUP DATABASE commands.

Well…. No.

msdb.dbo.rds_backup_database

The trick is, you’ve made it possible to get a SQL Server native backup. You didn’t make it possible to run the BACKUP command. Instead, you have to do something that looks like this:

EXEC msdb.dbo.rds_backup_database 
	@source_db_name = 'HamShackRadio',
    @s3_arn_to_backup_to = 'arn:aws:s3:::mybackup/HamShackRadio.bak',
    --[@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id'],	
    @overwrite_s3_backup_file = 1,
    @type = 'FULL'; --,
--[@number_of_files=n];

The deal is, you can get a native backup. That means, you can copy a file, in my case there, HamShackRadio.bak, and run a restore locally, or on another RDS instance. You can only take FULL or DIFFERENTIAL. You’ll note the parameter @kms_master_key_arn. That’s so you can encrypt the backup (a very good idea). I skipped setting up a key for this demo.

There’s also a command for restoring, msdb.dbo.rds_restore_database.

Conclusion

You can take control and backup SQL Server databases on RDS. As with so much else when we make the move to PaaS, there are differences from how we’re used to doing things. It’s OK once you understand. For a bunch of detail, go here. It took me a while to track down this information because it’s under import/export, not backup & restore.