SQL Server RDS Database Restore from S3 stuck in Restore Mode Summary:
We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it's stuck in &quot;Restore&quot; Mode and we can't do anything with it.
Detail:
We have spun-up a new SQL Server RDS Instance and we're trying to restore a database from back-up stored in S3. Which is simple enough, we had to do it in the past before maybe a year ago or so. We run the command from the AWS Documentation
EXEC msdb.dbo.rds_restore_database
 @restore_db_name = 'OurDB'
, @s3_arn_to_restore_from = 'arn:aws:s3:::bucket/SQLBackUp.bak'
, @with_norecovery = 1

And we know that command won't work if the Options Group and IAM roles aren't set up, so we've made sure they are. When we run the command it takes a few minutes but appears to run fine.
We run the command to check on the status
EXEC msdb.dbo.rds_task_status
 @db_name='OurDB'

And what as % complete grows over the next few minutes to 100% and the lifecycle value says &quot;SUCCESS&quot;. However when I try to access the database, I'm unable to and it says it's stuck restoring and the exact error message I get is &quot;The database OurDB is not accessible. (ObjectExplorer)&quot;
Whenever I search RESTORE DATABASE Stuck RDS SQL SERVER or variations of that, all the stuff I find is about doing a restore of the entire RDS Instance and not an individual database. Or if I'm able to find something about restoring a database that's stuck then it's not about RDS but about regular SQL Server.
Here are some screenshots of what I'm seeing too.
It's not stuck, it's just still in restore mode because you used @with_norecovery = 1. You need to restore database with recovery to finish the process.