Self-Learning: ALTER DATABASE (SQL Azure Database)

http://msdn.microsoft.com/en-us/library/windowsazure/ff394109.aspx

Modifies a database.

Syntax Conventions (SQL Azure Database)

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

<edition_options> ::= 
{
   (MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB) 
    |(EDITION = {'web' | 'business'})
}
[;]

Arguments

---

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 |40 |50] 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, 20GB, 30GB, 40GB, or 50GB. 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, 20GB, 30GB, 40GB, or 50GB 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 SQL Azure 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).

This syntax diagram demonstrates the supported arguments and options in SQL Azure Database.

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

• COLLATE
  
  
• <file_and_filegroup_options>
  
  <add_or_modify_files>
  
  <filespec>
  
  <add_or_modify_filegroups>
  
  <filegroup_updatability_option>
  
  
• <set_database_options>
  
  <optionspec>
  
  <auto_option>
  
  <change_tracking_option>
  
  <cursor_option>
  
  <database_mirroring_option>
  
  <date_correlation_optimization_option>
  
  <db_encryption_option>
  
  <db_state_option>
  
  <db_update_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.

Remarks

---

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.

SQL Azure 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 Accounts and Billing in SQL Azure.

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.

Important
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 masterdatabase when executing the ALTER DATABASE statement.