Export (0) Print
Expand All
Expand Minimize


SQL Server 2014

Creates a new database. You must be connected to the master database to create a new database.

Applies to: Azure SQL Database. For syntax related to SQL Server, see CREATE DATABASE (SQL Server Transact-SQL).

CREATE DATABASE database_name [ COLLATE collation_name ]
   ( <edition_options> [, ...n] ) 

<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' } 
To copy a database:
CREATE DATABASE destination_database_name
     AS COPY OF [source_server_name.] source_database_name

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


The name of the new database. This name must be unique on the SQL Database server and comply with the SQL Server rules for identifiers. For more information, see Identifiers.


Specifies the default 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 default collation, which is SQL_Latin1_General_CP1_CI_AS.

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


Specifies the service tier of the database. The available values are: 'web', 'business', 'basic', 'standard', and 'premium'. SQL Database V12 does not support Web and Business.

When EDITION is specified but MAXSIZE is not specified, MAXSIZE will be set to the most restrictive size that the edition supports.

Note Note

Business and Web service tiers will be retired September 2015. For more information, see Web and Business Edition Sunset FAQ. For detailed information on upgrading existing Web and Business databases to the new service tiers, see Upgrade SQL Database Web/Business Databases to New Service Tiers.


Specifies the maximum size of the database. MAXSIZE must be valid for the specified EDITION (service tier) Following are the supported MAXSIZE values and defaults (D) for the service tiers.







100 MB

500 MB

1 GB

√ (D)

2 GB

√ (D)

5 GB

10 GB

√ (D)

20 GB

30 GB

40 GB

50 GB

100 GB

150 GB

200 GB

250 GB

√ (D)

300 GB

400 GB

500 GB

√ (D)

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.


Specifies the performance level. 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.


The name of the database that is created by the database copy. This name must be unique on the (destination) SQL Database server and comply with the SQL Server rules for identifiers. For more information, see Identifiers.

AS COPY OF [source_server_name.]source_database_name

For copying a database to the same or a different SQL Database server.

Note Note

AS COPY OF cannot be used with any other CREATE DATABASE arguments.


The name of the SQL Database server where the source database is located. This parameter is optional when the source database and the destination database are to be located on the same SQL Database server.

Note: The AS COPY OF argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy.


The name of the database that is to be copied.

Azure SQL Database does not support the following arguments and options when using the CREATE DATABASE statement:

  • Parameters related to the physical placement of file, such as <filespec> and <filegroup>

  • External access options, such as DB_CHAINING and TRUSTWORTHY

  • Attaching a database

  • Service broker options, such as ENABLE_BROKER, NEW_BROKER, and ERROR_BROKER_CONVERSATIONS

  • Database snapshot

For more information about the arguments and the CREATE DATABASE statement, see CREATE DATABASE (SQL Server Transact-SQL).

Databases in Azure SQL Database have several default settings that are set when the database is created. For more information about these default settings, see the list of values in DATABASEPROPERTYEX (Transact-SQL).

MAXSIZE provides the ability to limit the size of the database. If the size of the database reaches its MAXSIZE you will receive 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.

Important note Important

The CREATE DATABASE statement must be the only statement in a Transact-SQL batch. You must be connected to the master database when executing the CREATE DATABASE statement.

To change the size, edition, or service objective values later, use ALTER DATABASE (Transact-SQL).

Database Copies

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process. The CREATE DATABASE statement will return control to the user after the entry in sys.databases is created but before the database copy operation is complete. In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress. You can monitor the copy process with the sys.dm_database_copies and sys.databases views. The sys.dm_operations_status view can be used as well as it returns the status of database operations including database copy. At the time the copy process completes successfully, the destination database is transactionally consistent with the source database. For more information about copying databases in SQL Database, see Copying Databases in Azure SQL Database.

Note Note

When a database is copied to a new database, the new database is created with the same service tier and performance level as the source database. For example, a copy of a Premium database with P1 performance level will be created as a new Premium database with P1 performance level.

The following syntax and semantic rules apply to your use of the AS COPY OF argument:

  • The source server name and the server name for the copy target may be the same or different. When they are the same, this parameter is optional and the server context of the current session will be used by default.

  • The source and destination database names must be specified, unique, and comply with the SQL Server rules for identifiers. For more information, see Identifiers.

  • The CREATE DATABASE statement must be executed within the context of the master database of the SQL Database server where the new database will be created.

  • After the copying completes, the destination database must be managed as an independent database. You can execute the ALTER DATABASE and DROP DATABASE statements against the new database independently of the source database. You can also copy the new database to another new database.

  • The destination database cannot be accessed until the copy process is complete. You can check the status of the copy process by querying the sys.dm_operations_status, or the state column in the sys.databases view or the percentage_complete column in the sys.dm_database_copies view on the destination SQL Database server.

    During the copy process, the state column of the sys.databases view shows Copying on the destination SQL Database server. In addition, the percentange_complete column of the sys.dm_database_copies shows the percentage of bytes that have been copied on the destination server.

  • The source database may continue to be accessed while the database copy is in progress.

Only the server-level principal login (created by the provisioning process) or members of the dbmanager database role can create databases. Both the source and target logical servers must belong to the same Azure subscription.

You must log in to the master database of an Azure SQL Database server in order to execute any CREATE DATABASE statements. See How to connect to an Azure SQL Database

This example creates a database with no other parameters specified.


This example adds a collation for the database, and specifies the edition to use. To view the collations on a database, you must first log in to the database. Once logged in, run this statement: SELECT name, collation_name FROM sys.databases;

CREATE DATABASE hito collate Japanese_Bushu_Kakusu_100_CS_AS_KS_WS 

This example specifies a maximum database size:


This creates a copy of a database:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft