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

Prerequisites

The RDS instance runs SQL Server 2012 or later.

Create a database

Execute the following statement to create a database:

Note When you create a database, do not specify a file path. ApsaraDB RDS generates a default file path.
CREATE DATABASE TestDb

Modify a database

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

  • Do not specify an invalid file path.

    For example, specify an invalid 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 set the recovery model to a model other than FULL.

    For example, set 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 change the database status to ONLINE when the database is in the OFFLINE state.

    For example, change the database status from OFFLINE 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 want to change the database status from OFFLINE to ONLINE, you can use the sp_rds_set_db_online stored procedure. Execute the following statement:

    EXEC sp_rds_set_db_online 'TestDb'

Delete a database

Execute the following statement to delete a database:

DROP DATABASE [TestDb]

If you have not backed up the database before deletion, 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] .