RDS Does Not Allow Restoring MS SQL DB from S3

Question

I have a question for you folks who might be familiar with the intricacies of restoring a .bak file from S3 to an AWS RDS MS SQL instance. Here is what I have tried, and how far I have gotten:

First some context: I am the sole IT guy at an all Mac, non-profit, special needs school (we help the kids who fall through the cracks). I recently received a backup of our SIS from a vendor who could only provide it by way of an MS SQL backup file, which ends in .bak. According to my research, I should be able to spin up a free AWS RDS instance of MS SQL, and then restore the .bak file to that free instance.

I have successfully created an AWS account with a free-tier RDS instance that has a singular, blank (brand new) MS SQL database called database-1. The instance is using MS SQL Express, version 14 (as far as I can tell). Other specific actions I have taken include the following, all taken from articles I have found online discussing how to do this:

1. Created an Option Group with the MSSQK-BACKUP-RESTORE option, and added that Option Group to the RDS database called database-1.

2. Created an IAM role called restoreadmin and gave it premissions to do everything on my S3 instance.

3. Created an S3 bucket called Achieve-DB, and uploaded the MS SQL backup file to it.

The articles I used to guide me through all of this, btw, are:

1. Amazon RDS Support for Native Backup and Restore in SQL Server
2. Secure Access to S3 Buckets Using IAM Roles

However, despite doing everything listed above, including banging my head on the keyboard a few times, I am not able to restore the backup from S3 to the database in RDS. The procedure for doing so, ostensibly, is the following:

1. Within the RDS console, select the database you would like to restore to,
2. Click the Restore from S3 button
3. Follow the wizard...

The problem I am running into is that when I click on the Restore from S3 button, the only two options for restoring are Amazon Aurora and MySQL.

Can anyone here suggest where I might be going wrong? Thank you!!

Answer 1

The quick answer is "don't waste your time with AWS and RDS". But there are nuances. Those solutions might be appropriate for some things but trying to open a MS SQL backup file on a Mac is not one of them. Here is what you want to do:

https://apple.stackexchange.com/questions/385430/how-to-read-ms-sql-backup-file-on-mac

The key to it all, for me, was this step by step article that walks you all the way through it:

https://learn.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15

Answer 2

The restore from S3 in RDS is not supported for RDS SQL Server database instances. If you want to restore backup of the database (.bak file), you need to use native backup/restore option [1]. After you associate SQLSERVER_BACKUP_RESTORE option to your option group, then you need to create a task on your RDS instance, for example:

exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup1.bak';

Note you can use any client application for this, including any application available for MacOS.

To monitor progress of your task, you can use query like this:

exec msdb.dbo.rds_task_status @task_id=5;

Note the task_id must be updated accordingly.

[1] - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html