ApsaraDB RDS:Create and manage databases by using SQL statements

Prerequisites

The RDS instance runs SQL Server 2012 or later.

Create a database

CREATE DATABASE TestDb

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

• Create or delete a database in the ApsaraDB RDS console or by calling an API operation. For more information, see the following topics:

  • ApsaraDB RDS console: Create a database or Delete a database

  • API operation: CreateDatabase or DeleteDatabase

• Change the character set collation and the time zone of a database in the ApsaraDB RDS console or by calling an API operation. For more information, see the following topics:

  • ApsaraDB RDS console: Change the character set collation and time zone of system databases

  • API operation: DescribeCollationTimeZones