CoryRetherford.com

 

To import a MSSQL .bak file from another location into an Amazon AWS RDS MSSQL instance you must follow these instructions, there is currently no other option for RDS MSSQL.

Create an S3 and verify the RDS MSSQL instance can access, this could be accomplished by modifying the VPC appropriately or granting public access to the S3. After this verify you can connect to the instance on port 1433 using SSMS or using telnet to the instance name such as the way mine looked:

When assessable run the following query within SSMS to import the .bak from S3 into RDS MSSQL.

exec msdb.dbo.rds_restore_database
@restore_db_name='DBNAME.bak',
@s3_arn_to_restore_from='arn:aws:s3:::s3bucketname/DBNAME.bak',
@with_norecovery=0,
@type='FULL';

You can track the following import process status using the following Native Tracking of Process guide.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Tracking

If you receive this error during import you must create an RDS "Option Group".

Msg 50000, Level 16, State 0, Procedure msdb.dbo.rds_restore_database, Line 80 [Batch Start Line 0]
Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later.
USAGE:
EXECUTE msdb.dbo.rds_restore_database @restore_db_name, @s3_arn_to_restore_from, [@kms_master_key_arn], [@type], [@with_norecovery]
@restore_db_name
: Name of the database being restored
@s3_arn_to_restore_from
: S3 ARN of the backup file used to restore database from.
@kms_master_key_arn
: KMS customer master key ARN to decrypt the backup file with.
@type
: The type of restore. Valid types are FULL or DIFFERENTIAL. Defaults to FULL.
@with_norecovery
: The recovery clause to use for the restore operation. Set this to 0, to restore with RECOVERY (database will be online after the restore).
Set this to 1, to restore with NORECOVERY (database will be left in the RESTORING state allowing for subsequent differential or log restores).
For FULL restore, defaults to 0.
For DIFFERENTIAL restores, you must specify 0 or 1.

Navigate to the Amazon RDS portal.

Click Options Group > Create Group

• Provide a non-space, no caps name (For example, mssqlse)
• Provide description (For example, MSSQL Standard Edition)
• Choose "sqlserver-se" for Standard Edition MSSQL
• Choose Engine Version (14.00 in this case)

• Click Create

The new Options Group is now displayed in the available Options Groups for your Amazon RDS portal page.

• Select the newly created Options Group and Add Option.
• Choose SQLSERVER_BACKUP_RESTORE for the Option Details name.
• Choose "Create Custom" from the IAM dropdown option.
• Choose immediately for the Scheduling option.
• Select Add option.

Back on the Amazon RDS DB Portal Page

• Select your DB instance and select Modify.
• Scroll down the Modify DB Instance page to Database Options
  • Change the Option group to your newly created group.
• Click Continue
• Under the Schduling of modifications section, select the Apply Immediately.
• Click Modify DB Instance.

This will associate a group that will permit the import of a database into the AWS RDS MSSQL instance.

I have done this for a Vended application and SharePoint 2019 successfully thus far, Happy importing!

Note - The folowing server-level roles are not available from within the AWS RDS MSSQL instance.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

• bulkadmin
  dbcreator
  diskadmin
  securityadmin
  serveradmin
  sysadmin