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

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.

Here are some screenshots of what I'm seeing too.

SQL Commands and Results Error in SSMS

4

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.

0
1

For RDS:

exec msdb.dbo.rds_finish_restore @db_name='database_name';
New contributor
eyasu is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
-1

...and unless there's something special about the RDS environment, you can just execute

RESTORE DATABASE ourDb WITH RECOVERY

to get you out of that restoring state.

1
  • RDS is special. You can't run that command. – Bo Anderson Oct 22 '20 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.