As of this writing, it is not possible to directly use the AWS console to restore an MSSQL database backup from AWS S3 Bucket to AWS RDS for SQL Server, unlike Aurora or MySQL databases.

But, it is still possible to restore an MSSQL database backup in an AWS S3 Bucket to an AWS RDS for SQL server instance with some intermediate steps.

This could be a cheaper, faster and easier process compared to having the backups stored on a block or network storage since AWS S3 buckets are a low-cost storage alternative and the steps could be scripted and automated.

A convenience of this process is that the MSSQL client could be located anywhere, on another EC2 instance in the same network or on an engineer's computer, since the backup would be stored on an AWS S3 bucket.

In addition, AWS RDS provides conveniently stored procedures such as the ability to connect directly to the AWS S3 bucket to retrieve the MSSQL database backup and restore it into the MSSQL instance in AWS RDS. This process is simpler compared to restoring an MSSQL database backup from a network drive or local drive.

Lastly, this process could be faster as the AWS RDS platform will have high-speed network connectivity to AWS S3 bucket (depending on the AWS RDS instance size), which would reduce the time to copy the data from the backup to the database.

The process, excluding the time taken to create the database backup and upload it into an AWS S3 bucket, could be completed in under 2 hours with these parameters;

• Creation of required AWS resources: <45 mins

  • Basic configurations

  • Instance size: db.m6i.xlarge

• Restore the database backup: <60 minutes

  • Database size: 180 GB

  • Backup file size: 150 GB

Steps

1. Create AWS S3 Bucket and upload the MSSQL database backup into it.

2. Create an AWS IAM role to access the AWS S3 Bucket with the database backup.

3. Create a custom AWS RDS option group with the SQLSERVER_BACKUP_RESTORE option.

   1. 

4. Create your AWS RDS for SQL Server instance with the custom AWS RDS option group.

5. Establish connectivity from a suitable MSSQL client to the newly created RDS instance.

6. Modify the following parameters in the query below.

   • @restore_db_name – The name of the database to restore. Database names are unique. You can't restore a database with the same name as an existing database.

   • @s3_arn_to_restore_from – The ARN indicating the Amazon S3 prefix and names of the backup files used to restore the database.

       exec msdb.dbo.rds_restore_database
           @restore_db_name='database_name',
           @s3_arn_to_restore_from='arn:aws:s3:::testBucket/testDBName.bak'
     
       # Example
       exec msdb.dbo.rds_restore_database
           @restore_db_name='testDBName',
           @s3_arn_to_restore_from='arn:aws:s3:::testBucket/testDBName.bak'
     

     

7. Open a new query window and run the modified query. The query would be executed successfully quickly and the process will run in the background.

8. To monitor the progress, another AWS RDS stored procedure can be used, exec msdb.dbo.rds_task_status.

   • The results from the stored procedure could then be used to estimate the remaining time for the restoration process.

     

9. Once the restoration process is completed, the database would be ready for use.

References

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html