Create and manage SQL Server databases using SQL-ApsaraDB RDS(RDS)-阿里云帮助中心

ApsaraDB RDS for SQL Server restricts certain native SQL Server operations to protect instance stability and backup integrity. This document covers how to create, delete, and rename databases using T-SQL, explains which ALTER DATABASE operations are blocked, and describes the correct alternatives for each.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for SQL Server instance running SQL Server 2012 or later

Create a database

Run the following statement:

CREATE DATABASE TestDb

RDS automatically assigns the data file path. Do not specify a file path in the statement.

To view the data file and log file paths for all databases on the instance:

SELECT db_name(database_id), physical_name
FROM sys.master_files;

Delete a database

Warning

Back up the database before deleting it. A deleted database can only be recovered from a backup.

Run the following statement:

DROP DATABASE [TestDb]

If no backup exists at the time of deletion, RDS displays an informational message alongside the success confirmation:

DROP DATABASE [TestDb]
-------------------------------------------------------------------------------------------------
Kindly reminder:
    your database [TestDb] does not exist any backup set.
-------------------------------------------------------------------------------------------------
Login User [Test11] has dropped database [TestDb] .

Rename a database

Use the sp_rds_modify_db_name stored procedure to rename a database. This stored procedure does not require additional authorization and can be executed by a standard account. Do not use the native ALTER DATABASE ... MODIFY NAME command or the SQL Server Management Studio (SSMS) GUI — both fail with a permission error because RDS grants accounts only CREATE DATABASE permission on the master database by default, which is insufficient for renaming.

Supported instance editions:

High Availability (HA) Series
Cluster Edition
Basic series

Syntax:

USE db
GO
EXEC sp_rds_modify_db_name 'db', 'new_db'
GO

Parameter	Description
First parameter	Original database name
Second parameter	New database name

On High Availability (HA) Series and Cluster Edition instances, renaming a database triggers an automatic rebuild of the replication configuration between the primary and secondary instances. During this process, RDS backs up and restores the database data. If the database is large, verify that the instance has enough free storage before renaming.

Database modification restrictions

Most ALTER DATABASE attributes can be modified. The following operations are blocked.

Moving a database to a different file path

Specifying an invalid file path in ALTER DATABASE ... MODIFY FILE returns an error:

ALTER DATABASE [TestDb] MODIFY FILE (NAME = N'TestDb', FILENAME = N'E:\KKKK\DDD\DATA\TestDb.mdf')

Msg 50000, Level 16, State 1, Procedure ******, Line 152
The file path [
E:\KKKK\DDD\DATA\TestDb.mdf ] is invalid, please specify correct path folder [ E:\mmm\gggg\ ].
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

Changing the recovery model to non-FULL

RDS requires the FULL recovery model to maintain the log chain for point-in-time restores. Attempting to switch to SIMPLE:

ALTER DATABASE [TestDb]  -- [TestDb] is the database name.
SET RECOVERY SIMPLE

RDS blocks the change and resets the recovery model to FULL:

Msg 50000, Level 16, State 1, Procedure ******, Line 46
Login User [Test11] can't change database [TestDb] recovery model.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

Important

If the transaction log is full, the only option for shrinkage is to truncate the log chain. If you must switch the recovery model to SIMPLE in an emergency — for example, to truncate an oversized log — understand that doing so disconnects the backup chain. All point-in-time restore tasks that span the moment of disconnection will fail. After running the statements, you can ignore the error messages; the log chain is then disconnected and the model is set to SIMPLE. You are responsible for the consequences.

Setting a database back to ONLINE after OFFLINE

Running ALTER DATABASE [TestDb] SET ONLINE directly after taking a database offline returns:

Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'TestDb', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Use the sp_rds_set_db_online stored procedure instead:

EXEC sp_rds_set_db_online 'TestDb'

Common errors

Error modifying an RDS SQL Server database name with the native SQL `ALTER DATABASE` command or the SSMS graphical interface?

Issue description

Using ALTER DATABASE ... MODIFY NAME returns:

Msg 5011, Level 14, State 2, Line 4
User does not have permission to alter database 'jmdb01', the database does not exist, or the database is not in a state that allows access checks.

Using the SSMS GUI returns: unable to rename db_name.

By default, all accounts — including standard and privileged accounts — are granted only CREATE DATABASE permission on the master database. This permission is not sufficient to rename a database.

If you need to understand the full permission model, the following table shows the effective rename permissions when using SA-level grants. Enabling SA permissions is not recommended; use sp_rds_modify_db_name instead.

Permission	Rename scope
`CREATE DATABASE` on `master` only	Cannot rename any database
Instance-level `CREATE ANY DATABASE`	Can rename databases the account owns (must be the database owner, not just a member of the `db_owner` role)
Instance-level `ALTER ANY DATABASE`	Can rename any database on which the account has `ALTER` permission

Cause

By default, user accounts in ApsaraDB RDS for SQL Server, including standard accounts and privileged accounts, are granted only the CREATE DATABASE permission on the master database. This permission is insufficient to rename a database. As a result, you cannot directly rename a database using native SQL or the SSMS GUI.
(Not recommended) You can enable system administrator (SA) permissions to rename a database. Then, you can use an account with SA permissions to manually grant the required permissions to a standard account or privileged account. The permissions are described as follows:
- An account that has only the CREATE DATABASE permission on the master database cannot rename any database.
- An account with the instance-level CREATE ANY DATABASE permission can rename databases that it owns. Note: The user must be the database owner, not just a member of the db_owner role. For more information, see Modify database properties (advanced information: db_owner).
- An account with the instance-level ALTER ANY DATABASE permission can rename any database on which it has the ALTER permission.

Solution

Use the sp_rds_modify_db_name stored procedure provided by ApsaraDB RDS for SQL Server to safely rename a database. This procedure does not require additional authorization and can be executed by standard accounts.

T-SQL command

sp_rds_modify_db_name

Supported instance editions

basic edition, high-availability edition, and cluster edition

Description

This stored procedure renames a database. Before you run this procedure, ensure that the account used for the connection has the required permissions on the target database and that the database is online.

On high-availability and cluster edition instances, renaming a database automatically rebuilds the replication configuration. This process involves backup and restore operations. If you are renaming a large database, monitor the available storage on the instance. If the storage is insufficient, you may need to scale up.

Usage

USE master
GO
EXEC sp_rds_modify_db_name 'db','new_db'
GO

The first parameter is the original name of the database.
The second parameter is the new name of the database.

What's next

Manage databases using the console: Create a database and Delete a database
Manage databases using the API: CreateDatabase and DeleteDatabase
Change the character set collation and time zone: Change the character set collation and time zone; API: DescribeCollationTimeZones
View and change database properties, shrink transaction logs, or update statistics: Manage database properties; API: ModifyDatabaseConfig