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

Posted by

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.

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.