The documentation is sooo not complete at AWS to get a dB restored from local to the AWS server. The error I had when i ran the stored proc (below) was “Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later.” These guys got me started, but I was still a long way off, this guy finished the missing link. The instructions below hopefully will help. I wished I would have recorded this but .. this article is my best memory of how I solved it.

Connecting was mental too – I added a firewall rule – but adding to the default one was the trick that made it work. Now, after doing all this, I see why – you have to make “groups” of this or that and then apply those groups. That is the key to making things work on RDS.

a) backup your database by logging into the LOCAL instance. R mouse click the dB, tasks, Backup

• To restore – you need to run a stored procedure
  • but first, you have to make an option and a IAM role (how to modify a role – go BACK to services and click IAM)
    • a guy here has some hints as to what to do
    • Under “Option Groups” create a new one. Call it OGrp1 (for fun). Choose all the fun options and do them correctly. Look at your dB instance for the details you require – they should match up
    •  Some how (and I lost how I did it, else I would include a link) you need to add the option_name: SQLSEVER_BACKUP_RESTORE
  • IAM – add a role
    • go to the Instances console for RDS. Now start again and under Services way at the top, click on IAM
      • add a role
        • attach a policy sqlNativeBackup
      • make sure the trust relationship is set (link missing)
      • name your IAM – I named mine backuprestore
      • You do not need users – if you leave users at none – it means the root user
  • Option: click here for general help
    • YOU ARE NOT DONE ONCE YOU MAKE AN OPTION – you have to then go to instances and click on “Modify” on the database of interest.
      • The option is called SQLSERVER_BACKUP_RESTORE
      • How to create an option group
    • Go to the instances as if you were going to look at your dB details BUT THEN…
    • Look down on the left and under “Option Groups” you choose your new named option
    • at the bottom click Apply Immediately, then click Modify
    • Wait about 2 minutes,you should see it say “pending” next to the option and delete-pending next to the old option
• Make an AWS bucket in the same region as the AWS dB server
• copy the path of the backup when you do so

Restore dB – again w. a stored proc

•  Syntax

  • [c]
    exec msdb.dbo.rds_restore_database @restore_db_name=’Slabsmith’, @s3_arn_to_restore_from=’arn:aws:s3:::mybucketName/MydB_20170126_1am.bak’;
    [/c]

Should work – sorry I missed some steps. I foundered my way through this – but I kinda got there with the blog posts I provided. The error I got