MS SQL Server RDS Database Restore from S3 gets Stuck Everytime

0

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 "Restore" 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)https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

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 "SUCCESS". 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 "The database OurDB is not accessible. (ObjectExplorer)"

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.

asked 3 years ago1055 views
2 Answers
0
Accepted Answer

Try running with:

@with_norecovery = 0

and see if that works for you. Setting it to 1 leaves it in a state where you can then restore additional transaction logs etc, or set up replication. If you want the database usable, you need to leave it in an online state, which setting it to 0 should do.

StuAD
answered 3 years ago
0

I'm dumb, thanks for figuring that out

answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions