How to Rename a Database on a SQL Server Amazon RDS Instance?

Attempts via SQL Server Management Studio and Transact SQL to rename a database hosted on a SQL Server Amazon RDS instance result in the following error.

User does not have permission to alter database 'Morningstar', the database does not exist, or the database is not in a state that allows access checks.

Note that this is the Transact SQL statement that I tried.

ALTER DATABASE <OldName> Modify Name = <NewName>;

The best option that I have come up with so far is to restore a copy of the original database with the new name and then delete the original. See my StackOverflow post for details on how to do this.

There are at least the following disadvantages to this approach.

1. The export and import operations for a bacpac can take a long time.
2. Any applications or websites that perform writes to the database will need to be taken offline during the whole process.
3. There are manual steps involved which can result in errors.
4. The RDS instance will need to be large enough to host two copies of the database if you want to leave the original there as a rollback strategy until the new one is available.

Can anyone suggest an alternative approach that does not have these disadvantages?

Update: I am interested in renaming a SQL Server database that is hosted within an RDS Instance - not the RDS Instance.

Solution 1:

RDS has recently added a stored procedure to accomplish this:

EXEC rdsadmin.dbo.rds_modify_db_name N'<OldName>', N'<NewName>'

Source: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.RenamingDB.html

Solution 2:

AWS provided the following info in the case that I opened (paraphrased).

Although the master user is a member of the db_owner database role, a database rename requires a change in sys.sysdbreg which is in the master database. Membership in the db_owner role does not provide this permission.

The workaround that I described in my question is the method that they would recommend. I have asked for confirmation that there is no easier means of copying databases from and to RDS than the one that I described in my StackOverflow post.

Solution 3:

1. Connect to the RDS MSSQL server
2. Open New Query window as master.
3. Copy and paste the below Query.

EXEC rdsadmin.dbo.rds_modify_db_name N'OLD Database name', N'New Database Name' GO

5. Change the 'OLD database Name' as your existing database name.
6. Change the 'New Database Name' as a name you want to give to the database.

Source: http://mytecksolution.blogspot.com/2017/09/rds-mssql-database-name-change-or-rename.html