Export (0) Print
Expand All

ALTER DATABASE (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see ALTER DATABASE.

Modifies a database. You must be connected to the master database to alter a database.

Syntax Conventions (Azure SQL Database)


ALTER DATABASE database_name 
{
    MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] ) 
  | SET { <set_database_options> } 
}

<edition_options> ::= 
{
    ( MAXSIZE = {1 | 5 | 10 | 20 | 30 … 150} GB ) 
  | ( EDITION = {'web' | 'business'} )
}

<set_database_options> ::= 
    <db_update_option>

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }
[;]

database_name
The name of the database to be modified.

MODIFY NAME =new_database_name
Renames the database with the name specified as new_database_name.

MODIFY (MAXSIZE = [1 | 5 | 10 | 20 | 30 … 150] GB)
Specifies the maximum size of the database. The maximum size must comply with the valid set of values for the EDITION property of the database. For Web Edition, valid values of MAXSIZE are: 1GB or 5GB. For Business Edition, valid values are 10GB increments up to 50GB, and then 50 GB increments. Changing the maximum size of the database may cause the database edition to be changed, also.

If MAXSIZE is set to a value of 1GB or 5GB and EDITION is not specified, the database edition will automatically be set to Web Edition.

If MAXSIZE is set to a value of 10GB or higher and EDITION is not specified, the database edition will automatically be set to Business Edition.

If neither MAXSIZE nor EDITION are specified a Web Edition database of size 1GB is created.

MODIFY (EDITION = ['web' | 'business'])
Changes the edition of the database. In this release, Microsoft Microsoft Azure SQL Database provides two database editions: Web Edition and Business Edition. Edition change will fail if the MAXSIZE property for the database is set to a value outside the valid range supported by that edition.

When EDITION is specified but MAXSIZE is not specified, MAXSIZE will be set to the most restrictive size that the edition supports (1 GB for Web Edition and 10 GB for Business Edition).

<db_update_option> ::=
Controls whether updates are allowed on the database.

{ READ_ONLY | READ_WRITE }

READ_ONLY
Users can read data from the database but not modify it.

READ_WRITE
The database is available for read and write operations.

noteNote
On Azure SQL Database federated databases, SET { READ_ONLY | READ_WRITE } is disabled.

The syntax diagram describes only the supported arguments and options in Microsoft Azure SQL Database.

Microsoft Azure SQL Database does not support the following ALTER DATABASE options:

  • <file_and_filegroup_options>:

    <add_or_modify_files>

    <filespec>

    <add_or_modify_filegroups>

    <filegroup_updatability_option>

  • Most <set_database_options>, as follows:

    <optionspec>

    <auto_option>

    <change_tracking_option>

    <cursor_option>

    <database_mirroring_option>

    <date_correlation_optimization_option>

    <db_encryption_option>

    <db_state_option>

    <db_user_access_option>

    <external_access_option>

    <parameterization_option>

    <recovery_option>

    <service_broker_option>

    <snapshot_option>

    <sql_option>

For more information about the arguments and the ALTER DATABASE statement, see ALTER DATABASE in SQL Server Books Online.

You can use the ALTER DATABASE statement to change the edition, name, and maximum size of your database after creation.

MAXSIZE provides the ability to limit the size of the database. If the size of the database reaches its MAXSIZE you will receive an error code 40544. When this occurs, you cannot insert or update data, or create new objects (such as tables, stored procedures, views, and functions). However, you can still read and delete data, truncate tables, drop tables and indexes, and rebuild indexes. You can then update MAXSIZE to a value larger than your current database size or delete some data to free storage space. There may be as much as a fifteen-minute delay before you can insert new data.

Changing the edition or maximum size of the database in a synchronous and offline operation and results in existing connections to be disconnected.

Microsoft Azure SQL Database is available in two editions: Web Edition and Business Edition. The Web Edition supports a database of up to 5 GB of data. The Business Edition supports a database of up to 50 GB of data.

To change the edition of your database after creation, use the MODIFY clause of the ALTER DATABASE statement to change the MAXSIZE and edition. For more information about editions and billing details, see Azure SQL Database Accounts and Billing.

Similarly, you can rename your database by using the MODIFY NAME clause of the ALTER DATABASE statement after creation. During the renaming process, connections to your database may be closed.

ImportantImportant
Only user databases can be renamed by using the ALTER DATABASE statement; a master database cannot be renamed. The ALTER DATABASE statement must be the only statement in a SQL batch. You must be connected to the master database when executing the ALTER DATABASE statement.

Only the server-level principal login (created by the provisioning process) or members of the dbmanager database role can alter a database.

securitySecurity Note
The owner of the database cannot alter the database unless they are a member of the dbmanager role.

Community Additions

Show:
© 2014 Microsoft