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;