SQL Server on Amazon RDS – unable to bring database online
This article will be short. Recently we had an issue trying to restore the database to the Amazon RDS version of the SQL Server. Long story short, we ended up with the database being offline with no ability to delete this database or bring it online. Typically these operations are available through the SQL Management Studio but we were receiving errors like this for “bring online”:
User does not have permission to alter database <name>, the database does not exist, or the database is not in a state that allows access checks. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5011)
And like this for “drop”:
Cannot drop the database <name>, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 3701)
To bring the database online in such case, you can use the following statement:
EXEC rdsadmin.dbo.rds_set_database_online <Name of database>
Interestingly, to take it offline, you don’t need any special functions, you can simply do this through SQL Management Studio or using query.
If you are still not able to delete the database, there is a possibility that your security credentials are not properly connected to the database. In this case, you can change the password of the master account of the database – this operation not only changes the password but also updates the security records for all databases and you should be able to remove the database. The database should be set online first.
thank you!
You are welcome 🙂
Thank you 🙂
You are welcome 🙂
Thank you! A very specific circumstance that, interestingly, rose to the top of my search results before I even started looking specifically for RDS as the culprit. You saved me a lot of time and I really appreciate it 🙂
Yes, this is indeed specific – we are facing such RDS and – in general – AWS specific issues from time to time, so I decided to write it down to keep it somewhere else than in my memory only 🙂 and it looks like it helped 🙂