All Products
Search
Document Center

ApsaraDB RDS:Create and manage databases by using SQL statements

Last Updated:Jan 26, 2024

This topic describes how to use SQL statements to create and manage databases on an ApsaraDB RDS for SQL Server instance. This topic also describes specific operations that are not allowed when you manage databases.

Prerequisites

The RDS instance runs SQL Server 2012 or later.

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] .

Usage notes for modifying a database

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

  • Do not specify an invalid file path for the database.

    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] specifies the database name. 
    SET RECOVERY SIMPLE

    The database log chain is disconnected. The system fails to set the recovery model of the database to SIMPLE and resets the recovery model to FULL. The system displays the following error messages:

    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. If you need 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.

  • Do not execute SQL statements to change the database status to ONLINE when the database is in the OFFLINE state.

    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.

    If you want to change the database status from OFFLINE to ONLINE, you can use the sp_rds_set_db_online stored procedure.

    EXEC sp_rds_set_db_online 'TestDb'

What to do next