restore from s3 bucket to sql server getting error Database backup/restore option is not enabled

Question

I got this kind of error when I tried to restore from s3 bucket

--error message

Msg 50000, Level 16, State 0, Procedure msdb.dbo.rds_restore_database, Line 49 [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] @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.

--executed code

exec msdb.dbo.rds_restore_database 
@restore_db_name='C2', 
@s3_arn_to_restore_from='arn:aws:s3:::slkk/31_May_2019_C2.bak'; 

Please help me. I am using aws free account. How do I enable backup restore?

Answer 1

Go to IAM and crete new Role with Permissions as follow:

• AWSBackupServiceRolePolicyForRestores
• AWSBackupServiceRolePolicyForBackup

Go to RDS > Option groups

• Create group (Name: SqlServerBackupRestore, Description: xxx, Engine: Select your DB engine, Major Engine Version: Select version of your DB instance.
• Click Create

Once group is created, click the name of created group to Edit.

• Click Add option
• Select option: SQLSERVER_BACKUP_RESTORE
• Select IAM role you created in previous steps
• Select Immediately to schedule instant change

Back to RDS > Databases and select your instance

• Click Modify
• Select Option group you created in previous step

• Click Next
• Select Apply immediately (it should not cause service downtime)

• Apply changes by clicking Modify DB instance
• If you have connection to DB from SQL Management Studio please close and connect again.

Answer 2

I know this is old, but I ran into the same error and was able to resolve it so I thought I'd share since this was unanswered.

First, this article details what you need, so make sure you follow these steps: https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/

I had setup the Options Group and everything initially. The steps I missed were these two:

3. Associate the option group with the DB instance by choosing Databases in the navigation pane, and then choose the instance to back up. Choose Actions, and then choose Modify.

4. Under Database Options, choose the option group that you created, and then choose Apply Immediately and Continue. Review the information, and then choose Modify DB Instance. This option group modification has no downtime because instance reboot is not required.

In other words, you have to modify the actual database instance with the new Option Group that was setup in those steps. Then once you login to your instance via SSMS, you can execute the restore by executing (taken from the article):

exec msdb.dbo.rds_restore_database 
    @restore_db_name='database_name', 
    @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';