Export (0) Print
Expand All

ALTER DATABASE (Transact-SQL)

Modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots cannot be modified. To modify database options associated with replication, use sp_replicationdboption.

Applies to: ( through current version), .

Because of its length, the ALTER DATABASE syntax is separated into the following topics:

ALTER DATABASE

The current topic provides the syntax for changing the name and the collation of a database.

ALTER DATABASE File and Filegroup Options

Provides the syntax for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.

ALTER DATABASE SET Options

Provides the syntax for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE Database Mirroring

Provides the syntax for the SET options of ALTER DATABASE that are related to database mirroring.

ALTER DATABASE SET HADR

Provides the syntax for the options of ALTER DATABASE for configuring a secondary database on a secondary replica of an AlwaysOn availability group.

ALTER DATABASE Compatibility Level

Provides the syntax for the SET options of ALTER DATABASE that are related to database compatibility levels.

Topic link icon Transact-SQL Syntax Conventions

-- SQL Server Syntax
ALTER DATABASE { database_name  | CURRENT }
{
    MODIFY NAME = new_database_name 
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | <set_database_options>
}
[;]

<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> ::= 
  <delayed_durability_option> ::=  <external_access_option> ::=
  <FILESTREAM_options> ::=
  <HADR_options> ::=  
  <parameterization_option> ::=
  <recovery_option> ::= 
  <service_broker_option> ::=
  <snapshot_option> ::=
  <sql_option> ::= 
  <termination> ::=
-- Azure SQL Database Syntax
ALTER DATABASE database_name 
{
    MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] ) 
  | SET { <set_database_options> } 
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'Premium' } 
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' } 
}

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

database_name

Is the name of the database to be modified.

Note Note

This option is not available in a Contained Database.

CURRENT

Applies to: through .

Designates that the current database in use should be altered.

MODIFY NAME =new_database_name

Renames the database with the name specified as new_database_name.

COLLATE collation_name

Applies to: through .

Specifies the collation for the database. collation_name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the collation of the instance of .

For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500] GB)

Applies to:

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. Changing the maximum size of the database may cause the database EDITION to be changed. Following table lists the supported MAXSIZE values and the defaults (D) for the service tiers.

MAXSIZE

Web

Business

Basic

Standard

Premium

100 MB

√ (D)

500 MB

√ (D)

1 GB

√ (D)

2 GB

5 GB

10 GB

√ (D)

√ (D)

20 GB

30 GB

40 GB

50 GB

100 GB

150 GB

200 GB

250 GB

300 GB

400 GB

500 GB

The following rules apply to MAXSIZE and EDITION arguments:

  • The MAXSIZE value, if specified, has to be a valid value shown in the table above.

  • If MAXSIZE is set to less than 5 GB, and EDITION is not specified, the database edition will automatically be set to Web.

  • If MAXSIZE is set to greater than 5 GB, and EDITION is not specified, the database edition will automatically be set to Business.

  • If EDITION is specified but MAXSIZE is not specified, the default value for the edition is used. For example, is the EDITION is set to Standard, and the MAXSIZE is not specified, then the MAXSIZE is automatically set to 500 MB.

  • If neither MAXSIZE nor EDITION is specified, the EDITION is set to Web, and MAXSIZE is set to 1 GB.

MODIFY (EDITION = [ 'web' | 'business' | 'basic' | 'standard' | 'premium' ] )

Applies to:

Changes the edition of the database.   service tiers can be set or modified using the EDITION parameter. EDITION change will fail if the MAXSIZE property for the database is set to a value outside the valid range supported by that edition.

Important note Important

Business and Web service tiers will be retired September 2015. For more information, see Web and Business FAQ.

SERVICE_OBJECTIVE

Applies to:

Specifies the performance level. The current options are shared, basic, S1, S2, P1, P2, and P3. For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Azure SQL Database Service Tiers and Performance Levels. If the specified SERVICE_OBJECTIVE is not supported by the EDITION you receive an error. To change the SERVICE_OBJECTIVE value from one tier to another (for example from S1 to P1), you must also change the EDITION value.

<db_update_option> ::=

Applies to:

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.

Note Note

On federated databases, SET { READ_ONLY | READ_WRITE } is disabled.

<delayed_durability_option> ::=

Applies to: through .

For more information see ALTER DATABASE SET Options (Transact-SQL) and Transaction Durability.

<file_and_filegroup_options >::=

For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

To remove a database, use DROP DATABASE.

To decrease the size of a database, use DBCC SHRINKDATABASE.

The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

The state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States. The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.

When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The exception is setting database mirroring options. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.

The plan cache for the instance of is cleared by setting one of the following options.

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

PAGE_VERIFY

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the error log contains the following informational message: " has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval. 

The procedure cache is also flushed in the following scenarios:

  • A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.

  • You run several queries against a database that has default options. Then, the database is dropped.

  • A database snapshot for a source database is dropped.

  • You successfully rebuild the transaction log for a database.

  • You restore a database backup.

  • You detach a database.

Changing the Database Collation

Before you apply a different collation to a database, make sure that the following conditions are in place:

  1. You are the only one currently using the database.

  2. No schema-bound object depends on the collation of the database.

    If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASEdatabase_nameCOLLATE statement will fail. will return an error message for each object blocking the ALTER action:

    • User-defined functions and views created with SCHEMABINDING.

    • Computed columns.

    • CHECK constraints.

    • Table-valued functions that return tables with character columns with collations inherited from the default database collation.

    Dependency information for non-schema-bound entities is automatically updated when the database collation is changed.

Changing the database collation does not create duplicates among any system names for the database objects. If duplicate names result from the changed collation, the following namespaces may cause the failure of a database collation change:

  • Object names such as a procedure, table, trigger, or view.

  • Schema names

  • Principals such as a group, role, or user.

  • Scalar-type names such as system and user-defined types.

  • Full-text catalog names.

  • Column or parameter names within an object.

  • Index names within a table.

Duplicate names resulting from the new collation will cause the change action to fail, and will return an error message specifying the namespace where the duplicate was found.

Viewing Database Information

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

Requires ALTER permission on the database.

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

Security note Security Note

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

A. Changing the name of a database

The following example changes the name of the AdventureWorks2012 database to Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

B. Changing the collation of a database

The following example creates a database named testdb with the SQL_Latin1_General_CP1_CI_AS collation, and then changes the collation of the testdb database to COLLATE French_CI_AI.

Applies to: through .

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft