ALTER DATABASE (Transact-SQL)

Modifies one or more properties of a database or its file(s). For databases that are not in SIMPLE or BULK_LOGGED recovery model, you can use ALTER DATABASE to take the database offline and bring it back online.

ALTER DATABASE <database_name>
{
    MODIFY NAME = <new_database_name>
    | SET <option_name> [ ,...n ]
    | COLLATE <collation_name>
    | ... [ other options ] ...
}

<database_name>

Is the name of the database to be modified.

MODIFY NAME = <new_database_name>

Changes the name of the database to <new_database_name>. The new name must be unique.

SET <option_name> [ ,...n ]

Specifies one or more database options to be set. Options include, but are not limited to:

• SINGLE_USER: Allows only one user to access the database.
• MULTI_USER: Allows multiple users to access the database.
• RESTRICTED_USER: Allows only members of the db_owner fixed database role and members of the dbcreator fixed server role.
• READ_ONLY: Makes the database read-only.
• OFFLINE: Takes the database offline.
• ONLINE: Brings the database online.

COLLATE <collation_name>

Specifies the default collation for the database. If not specified, the database inherits the collation of the SQL Server instance.

... [ other options ] ...

This represents various other clauses that can be used with ALTER DATABASE, such as modifying file properties (MODIFY FILE), setting database filegroups, and more. For a complete list, please refer to the official Microsoft documentation.

Requires ALTER ANY DATABASE permission on the server, or CREATE DATABASE permission, or membership in the db_owner fixed database role.

Example 1: Renaming a database

ALTER DATABASE MyDatabase
MODIFY NAME = MyNewDatabase;
            

Example 2: Setting a database to read-only

ALTER DATABASE MyDatabase
SET READ_ONLY = ON;
            

Example 3: Taking a database offline

ALTER DATABASE MyDatabase
SET OFFLINE WITH ROLLBACK IMMEDIATE;