This topic describes how to create and manage databases in an ApsaraDB RDS SQL Server instance by using SQL statements.

Prerequisites

The instance version must be ApsaraDB RDS SQL Server 2012 or later.

Create a database

Execute the following statement to create a database:

Note A default path is generated when you create a database in ApsaraDB for RDS. Do not specify any file path.
CREATE DATABASE TestDb

Modify a database

You can modify most database attributes. Do not perform the following operations:

  • Do not specify an incorrect file path.

    For example, specify an incorrect file path by executing the following 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 specify the recovery model to a model other than FULL.

    For example, specify the recovery model to SIMPLE by executing the following statements:

    ALTER DATABASE [TestDb]
    SET RECOVERY SIMPLE

    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.
  • Do not set the database status to ONLINE directly when it is in the OFFLINE state.

    For example, set the OFFLINE database to ONLINE by executing the following statements:

    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 need to set the database status to ONLINE, you can use the sp_rds_set_db_online stored procedure. Execute the following statement:

    EXEC sp_rds_set_db_online 'db'

Delete a database

Execute the following statement to delete a database:

DROP DATABASE [TestDb]

If you have not backed up the database before deleting, the system displays the following error messages:

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