Create and manage SQL Server databases using SQL - ApsaraDB RDS

This topic describes how to use SQL commands to create and delete databases on an ApsaraDB RDS for SQL Server instance. This topic also describes how to use a dedicated stored procedure to change a database name and describes the high-risk operations that are prohibited during database management.

Prerequisites

The ApsaraDB RDS for SQL Server instance must run SQL Server 2012 or a later version.

Create a database

Execute the following statement to create a database:

CREATE DATABASE TestDb

Note

When you create a database on the RDS instance, the system automatically generates a file path. Do not specify a file path.

You can execute the following statement to query the path information about data files and log files of all databases of an SQL Server instance:

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

Delete a database

Execute the following statement to delete a database:

DROP DATABASE [TestDb]

If you do not back up the database before you delete the database, the system displays the following error message:

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

Change a database name

ApsaraDB RDS for SQL Server provides the sp_rds_modify_db_name stored procedure to safely change a database name. This stored procedure does not require additional authorization and can be executed by a standard account.

T-SQL commands

sp_rds_modify_db_name

Supported RDS instances

High Availability (HA) Series
Cluster Edition
Basic series

Description

This stored procedure is used to rename a database. After you rename a database on an RDS instance that runs RDS High-availability Edition or RDS Cluster Edition, the system automatically rebuilds the replication configuration between the RDS instance and its secondary instance. During the rebuild process, the data of the RDS instance is backed up and restored. If the database occupies a large amount of storage, make sure that the available storage of the RDS instance is sufficient.

Usage

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

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

Database modification restrictions

You can modify most database attributes. Take note of the following points:

Do not move the database to an invalid file path.

For example, you execute the following statement but specify an invalid file path in the statement:

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

The system displays the following error messages:

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.

Do not set the recovery model to a model other than FULL.
For example, you execute the following statements to set the recovery model to SIMPLE:
```
ALTER DATABASE [TestDb]  --[TestDb] is the database name.
SET RECOVERY SIMPLE
```
This action breaks the database log chain. The system resets the recovery model to FULL, prevents the change to SIMPLE, and returns the following error message:
```
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 log is full, you can only truncate the log chain for shrinkage. We recommend that you do not change the recovery model of a database to SIMPLE. If you change the recovery model of a database to SIMPLE, the backup chain of the RDS instance to which the database belongs is disconnected, and all restoration tasks that are executed at the point in time when the backup chain is disconnected fail. To change the recovery mode of a database to SIMPLE and truncate the database log chain in an emergency situation, you must understand and are responsible for the preceding risks. After you execute the preceding statements, you can ignore the error messages that are displayed, and the database log chain is disconnected.

After you set a database to OFFLINE, do not directly run the ONLINE command.

For example, you execute the following statements to change the database status from OFFLINE to ONLINE:

USE [master]
  GO
  --set offline
  --ALTER DATABASE [TestDb]
  --SET OFFLINE
  --WITH ROLLBACK AFTER 0
  ALTER DATABASE [TestDb]
  SET ONLINE

The system displays the following error messages:

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.

To change the database status from OFFLINE to ONLINE, run the sp_rds_set_db_online stored procedure:

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

You receive an error when you try to rename an ApsaraDB RDS for SQL Server database using the native SQL ALTER DATABASE command or the SQL Server Management Studio (SSMS) graphical user interface (GUI). The following error messages are returned:

Error from the native SQL ALTER DATABASE command

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.

Error from the SSMS GUI: unable to rename db_name.

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.