Backing up an RDS MS SQL Server Database to an S3 Bucket

Note: I’m new to both MS SQL Server and to RDS databases. My base expertise is as a Db2 (LUW) DBA.

There is a really complete document on this available from Amazon.

Scenario

In this scenario, I have just spun up a new RDS instance for a project using a CloudFormation template. I’ll blog about that some day, but for now, I’m too new to really do that topic justice. All I did was take a yaml file that a colleague made and change the values for a new environment. The part that is covered here is taking a backup of the existing environment and restoring it into the new one.

Tools

While I’m a command line fan in the Db2 world, I’m using SSMS here, which painfully only runs on Windows, so I’m running it from a Windows VM on my Mac. That said I’m mostly only using the “Run Query” functionality, so I’m taking a fancy gui and using it like a command line.

Procedure

Back up Source Database

This was the easy part. After connecting via SSMS to the existing environment, I used an RDS stored procedure to take a database backup to an S3 bucket in the same region. There are just 4 parameters that I specified as input:

• source_db_name – the name of the database that I’m backing up
• s3_arn_to_backup_to – the S3 bucket to use for the backup, and the file name for the backup
• overwrite_S3_backup_file – I specified 1. This means that if I need to run the same command twice, it’ll replace the backup from the previous command.
• type – I specified `’FULL’` for a full backup.

Note that there are encryption options during backup as well, see the link at the top of this article for more details there.
The command I used in a “New Query” window was this (names changed to protect the innocent):

exec msdb.dbo.rds_backup_database
        @source_db_name='projprod',
        @s3_arn_to_backup_to='arn:aws:s3:::mssql-backups/proj/proj_prod_20191119.bak',
        @overwrite_S3_backup_file=1,
        @type='FULL';

The output from that looked like this:

To verify that the backup was successful, I used this command:

exec msdb.dbo.rds_task_status @task_id=5

And the output from that looked like this:

When the backup is complete, you should also be able to see it in your AWS console for s3.

Restore into New RDS Instance

Once I had the backup on S3, I connected to it using SSMS, and then could easily do the restore of the database. There are only two parameters I specify here:

• restore_db_name – this is the database name to restore into. For the Db2 DBAs reading, this is what I would specify after the INTO keyword of the RESTORE command if I wanted to change the database name on restore.
• S3_arn_to_restore_from – this is the same S3 bucket and file name I specified on backup.

The command I used in a “New Query” window was this (names changed to protect the innocent):

exec msdb.dbo.rds_restore_database
  @restore_db_name = 'projbeta',
  @S3_arn_to_restore_from = 'arn:aws:s3:::mssql-backups/proj/proj_prod_20191119.bak';

Again, I can check status with:

exec msdb.dbo.rds_task_status @task_id=1

The results look like this:

And when complete:

Other Comments

In general, a backup of an MS SQL Server database looks to be as simple as the backup and restore of a Db2 database, at least on RDS, and nowhere near as complicated as setting up RMAN, at least for those unfamiliar with the platform.

I worry that I’m missing some essential steps on the encryption, and that is one of the things on my list to better understand. There’s a lot on that list as I delve RDBMSes other than Db2.