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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *