ALTER DATABASE (TSQL Statement)

changes the attributes of the specified database.

Syntax

ALTER DATABASE database_name MODIFY NAME = new_database_name

ALTER DATABASE database_name SET { ONLINE | OFFLINE | CORRUPTED }

ALTER DATABASE database_name SET <option> [ ,... n ]

<option> ::=
{     <db_update_option>
    | <db_access_option>
}

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_access_option> ::=
    { RESTRICTED_USER | MULTI_USER }

Arguments

database_name
is the name of the database.
new_database_name
is the new name of the database.
ONLINE
puts the database online. This is the normal state for a database.
OFFLINE
puts the database offline, when you don’t want anyone to access the database.
CORRUPTED
exactly the same as OFFLINE, and flags the database as corrupted.
READ_ONLY
in this mode, the database is accessible for SELECT, but not for any write operation.
READ_WRITE
in this mode, the database is accessible for read and write operations. This is the normal mode for a database.
RESTRICTED_USER
only sa and dbo can access the database.
MULTI_USER
all users can access the database.

Remarks

Only sa is allowed to execute this statement.

The statement ALTER DATABASE waits until all sessions currently accessing the database have finished their work, as it requires an exclusive lock on the database.

If you change a database name, e.g. from [customers] to [clients], you will probably want to change also the default database of the logins from [customers] to [clients].

You can easily do this with the following SHOW command. It will create the proper ALTER LOGIN statements, and you just have to copy-paste and run them.

SHOW TEMPLATE = '{{if eq .default_database_name "customers"}}ALTER LOGIN [{{.login_name}}] WITH DEFAULT_DATABASE=[clients];{{end}}' LOGINS

This command will print lines like:

ALTER LOGIN [bob] WITH DEFAULT_DATABASE=[clients];
...

Examples

ALTER DATABASE customers MODIFY NAME = clients;

ALTER DATABASE clients SET OFFLINE;

ALTER DATABASE mydb SET READ_ONLY;