CREATE DATABASE (Transact-SQL)

Syntax

CREATE DATABASE database_name 
    [ ON 
        [ PRIMARY ] [ <filespec> [ ,...n ] 
        [ , <filegroup> [ ,...n ] ] 
    [ LOG ON { <filespec> [ ,...n ] } ] 
    ] 
    [ COLLATE collation_name ]
    [ WITH <external_access_option> ]
]
[;]

To attach a database
CREATE DATABASE database_name 
    ON <filespec> [ ,...n ] 
    FOR { ATTACH [ WITH <service_broker_option> ]
        | ATTACH_REBUILD_LOG }
[;]

<filespec>::= 
{
(
    NAME =logical_file_name,
    FILENAME = { 'os_file_name' | 'filestream_path' } 
        [ , SIZE =size [ KB | MB | GB | TB ] ] 
        [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
        [ , FILEGROWTH =growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,...n ]
}

<filegroup> ::= 
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]
    <filespec> [ ,...n ]
}

<external_access_option> ::=
{
  [ DB_CHAINING { ON | OFF } ]
  [ , TRUSTWORTHY { ON | OFF } ]
}
<service_broker_option> ::=
{
    ENABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
}

Create a database snapshot
CREATE DATABASE database_snapshot_name 
    ON 
    (
        NAME = logical_file_name,
        FILENAME ='os_file_name' 
    ) [ ,...n ] 
    AS SNAPSHOT OF source_database_name
[;]

Arguments

• database_name
  Is the name of the new database. Database names must be unique within an instance of SQL Server and comply with the rules for identifiers.

  database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. If a logical log file name is not specified, SQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.

  If data file name is not specified, SQL Server uses database_name as both the logical_file_name and as the os_file_name. The default path is obtained from the registry. The default path can be change by using the Server Properties (Database Settings Page) in Management Studio. Changing the default path requires restarting SQL Server.

• ON
  Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined. ON is required when followed by a comma-separated list of <filespec> items that define the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items that define user filegroups and their files.

• PRIMARY
  Specifies that the associated <filespec> list defines the primary file. The first file specified in the <filespec> entry in the primary filegroup becomes the primary file. A database can have only one primary file. For more information, see Files and Filegroups Architecture.

  If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

• LOG ON
  Specifies that the disk files used to store the database log, log files, are explicitly defined. LOG ON is followed by a comma-separated list of <filespec> items that define the log files. If LOG ON is not specified, one log file is automatically created that has a size that is 25 percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger. LOG ON cannot be specified on a database snapshot.

• COLLATE collation_name
  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 of the instance of SQL Server. A collation name cannot be specified on a database snapshot.

  A collation name cannot be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses. For information about how to change the collation of an attached database, visit this Microsoft Web site.

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

• FOR ATTACH
  Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files.

  FOR ATTACH requires the following:

  • All data files (MDF and NDF) must be available.

  • If multiple log files exist, they must all be available.

  If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

  Note

  A database created by a more recent version of SQL Server cannot be attached in earlier versions. The source database must be at least version 80 (SQL Server 2000) to attach to SQL Server 2008. SQL Server 2000 or SQL Server 2005 databases that have a compatibility level less than 80 will be set to compatibility 80 when they are attached.

  In SQL Server, any full-text files that are part of the database that is being attached will be attached with the database. To specify a new path of the full-text catalog, specify the new location without the full-text operating system file name. For more information, see the Examples section.

  FOR ATTACH cannot be specified on a database snapshot.

  Security Note

  We recommend that you do not attach databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

  For more information about attaching and detaching databases, see Detaching and Attaching Databases.

  Note

  If the database uses Service Broker, also see <service_broker_option>.

  For information about file permissions that are set whenever a database is detached and attached, see Securing Data and Log Files.

  When you attach a replicated database that was copied instead of being detached, consider the following:

  • If you attach the database to the same server instance and version as the original database, no additional steps are required.

  • If you attach the database to the same server instance but with an upgraded version, you must execute sp_vupgrade_replication to upgrade replication after the attach operation is complete.

  • If you attach the database to a different server instance, regardless of version, you must execute sp_removedbreplication to remove replication after the attach operation is complete.

  Note

  Attach works with the vardecimal storage format, but the SQL Server Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2. You cannot attach a database using vardecimal storage format to an earlier version of SQL Server. For more information about the vardecimal storage format, see Storing Decimal Data As Variable Length.

  For information about how to upgrade a database by using attach, see How to: Upgrade a Database Using Detach and Attach (Transact-SQL).

• FOR ATTACH_REBUILD_LOG
  Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log file is rebuilt. There must be a <filespec> entry specifying the primary file.

  Note

  If the log files are available, the Database Engine will use those files instead of rebuilding the log files.

  FOR ATTACH_REBUILD_LOG requires the following:

  • A clean shutdown of the database.

  • All data files (MDF and NDF) must be available.

  Important

  This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed. For more information, see BACKUP (Transact-SQL).

  Typically, FOR ATTACH_REBUILD_LOG is used when you copy a read/write database with a large log to another server where the copy will be used mostly, or only, for read operations, and will therefore require less log space than the original database.

  FOR ATTACH_REBUILD_LOG cannot be specified on a database snapshot.

  For more information about attaching and detaching databases, see Detaching and Attaching Databases.

• <filespec>
  Controls the file properties.

• NAME logical_file_name
  Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. A FILESTREAM filegroup cannot be named PRIMARY.

  • logical_file_name
    Is the logical name used in SQL Server when referencing the file. Logical_file_name must be unique in the database and comply with the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.

• FILENAME { 'os_file_name' | 'filestream_path' }
  Specifies the operating system (physical) file name.

  • 'os_file_name'
    Is the path and file name used by the operating system when you create the file. The file must reside on one of the following devices: the local server on which SQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. The specified path must exist before executing the CREATE DATABASE statement. For more information, see "Database Files and Filegroups" in the Remarks section.

    SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.

    If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one data file can be created on each raw partition.

    Data files should not be put on compressed file systems unless the files are read-only secondary files, or the database is read-only. Log files should never be put on compressed file systems. For more information, see Read-Only Filegroups and Compression.

  • 'filestream_path'
    For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.

    The filegroup and file (<filespec>) must be created in the same statement. There can be only one file, <filespec>, for a FILESTREAM filegroup.

    The SIZE, MAXSIZE, and FILEGROWTH properties do not apply to a FILESTREAM filegroup.

• SIZE size
  Specifies the size of the file.

  SIZE cannot be specified when the os_file_name is specified as a UNC path. SIZE does not apply to a FILESTREAM filegroup.

  • size
    Is the initial size of the file.

    When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

    The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. Size is an integer value. For values greater than 2147483647, use larger units.

• MAXSIZE max_size
  Specifies the maximum size to which the file can grow. MAXSIZE cannot be specified when the os_file_name is specified as a UNC path. MAXSIZE does not apply to a FILESTREAM filegroup.

  • max_size
    Is the maximum file size. The KB, MB, GB, and TB suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full. Max_size is an integer value. For values greater than 2147483647, use larger units.

• UNLIMITED
  Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

• FILEGROWTH growth_increment
  Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path. FILEGROWTH does not apply to a FILESTREAM filegroup.

  • growth_increment
    Is the amount of space added to the file every time new space is required.

    The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.

    A value of 0 indicates that automatic growth is off and no additional space is allowed.

    If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.

    Note

    In SQL Server, the default growth increment for data files has changed from 10% to 1 MB. The log file default of 10% remains unchanged.

• <filegroup>
  Controls the filegroup properties. Filegroup cannot be specified on a database snapshot.

• FILEGROUP filegroup_name
  Is the logical name of the filegroup.

  • filegroup_name
    filegroup_name must be unique in the database and cannot be the system-provided names PRIMARY and PRIMARY_LOG. The name can be a character or Unicode constant, or a regular or delimited identifier. The name must comply with the rules for identifiers.

  • CONTAINS FILESTREAM
    Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

  • DEFAULT
    Specifies the named filegroup is the default filegroup in the database.

• <external_access_option>
  Controls external access to and from the database.

  • DB_CHAINING { ON | OFF }
    When ON is specified, the database can be the source or target of a cross-database ownership chain.

    When OFF, the database cannot participate in cross-database ownership chaining. The default is OFF.

    Important

    The instance of SQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). When cross db ownership chaining is 1 (ON), all user databases can participate in cross-database ownership chains, regardless of the value of this option. This option is set by using sp_configure.

    To set this option, requires membership in the sysadmin fixed server role. The DB_CHAINING option cannot be set on these system databases: master, model, tempdb.

    For more information, see Ownership Chains.

  • TRUSTWORTHY { ON | OFF }
    When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database.

    When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.

    TRUSTWORTHY is set to OFF whenever the database is attached.

    By default, all system databases except the msdb database have TRUSTWORTHY set to OFF. The value cannot be changed for the model and tempdb databases. We recommend that you never set the TRUSTWORTHY option to ON for the master database.

    To set this option, requires membership in the sysadmin fixed server role.

• <service_broker_option>
  Controls Service Broker options on the database.

  Service Broker options can only be specified when the FOR ATTACH clause is used.

  • ENABLE_BROKER
    Specifies that Service Broker is enabled for the specified database. That is, is_broker_enabled is set to true in the sys.databases catalog view and message delivery is started.

  • NEW_BROKER
    Creates a new service_broker_guid value in both sys.databases and the restored database and ends all conversation endpoints with clean up. The broker is enabled, but no message is sent to the remote conversation endpoints.

  • ERROR_BROKER_CONVERSATIONS
    Ends all conversations with an error stating that the database is attached or restored. The broker is disabled until this operation is completed and then enabled.

• database_snapshot_name
  Is the name of the new database snapshot. Database snapshot names must be unique within an instance of SQL Server and comply with the rules for identifiers. database_snapshot_name can be a maximum of 128 characters.

• ON ( NAME =logical_file_name, FILENAME ='os_file_name') [ ,... n ]
  For creating a database snapshot, specifies a list of files in the source database. For the snapshot to work, all the data files must be specified individually. However, log files are not allowed for database snapshots.

  For descriptions of NAME and FILENAME and their values see the descriptions of the equivalent <filespec> values.

  Note

  When you create a database snapshot, the other <filespec> options and the keyword PRIMARY are disallowed.

• AS SNAPSHOT OF source_database_name
  Specifies that the database being created is a database snapshot of the source database specified by source_database_name. The snapshot and source database must be on the same instance.

  For more information, see "Database Snapshots" in the Remarks section.

Remarks

The master database should be backed up whenever a user database is created, modified, or dropped.

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.

You can use one CREATE DATABASE statement to create a database and the files that store the database. SQL Server implements the CREATE DATABASE statement by using the following steps:

1. The SQL Server uses a copy of the model database to initialize the database and its metadata.

2. A service broker GUID is assigned to the database.

3. The Database Engine then fills the rest of the database with empty pages, except for pages that have internal data that records how the space is used in the database. For more information, see Database File Initialization.

A maximum of 32,767 databases can be specified on an instance of SQL Server.

Each database has an owner that can perform special activities in the database. The owner is the user that creates the database. The database owner can be changed by using sp_changedbowner.

Database Files and Filegroups

Database Snapshots

Database Options

The model Database and Creating New Databases

Viewing Database Information

Permissions

Requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few login accounts.

Permissions on Data and Log Files

Examples

A. Creating a database without specifying files

USE master;
GO
CREATE DATABASE mytest;
GO
-- Verify the database files and sizes
SELECT name, size, size*1.0/128 AS [Size in MBs] 
FROM sys.master_files
WHERE name = N'mytest';
GO

B. Creating a database that specifies the data and transaction log files

USE master;
GO
CREATE DATABASE Sales
ON 
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

C. Creating a database by specifying multiple data and transaction log files

USE master;
GO
CREATE DATABASE Archive 
ON
PRIMARY  
    (NAME = Arch1,
    FILENAME = 'D:\SalesData\archdat1.mdf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch2,
    FILENAME = 'D:\SalesData\archdat2.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch3,
    FILENAME = 'D:\SalesData\archdat3.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
LOG ON 
   (NAME = Archlog1,
    FILENAME = 'D:\SalesData\archlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
   (NAME = Archlog2,
    FILENAME = 'D:\SalesData\archlog2.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) ;
GO

D. Creating a database that has filegroups

USE master;
GO
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
    FILENAME = 'D:\SalesData\SPri1dat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
( NAME = SPri2_dat,
    FILENAME = 'D:\SalesData\SPri2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
    FILENAME = 'D:\SalesData\SG1Fi1dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
    FILENAME = 'D:\SalesData\SG1Fi2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
    FILENAME = 'D:\SalesData\SG2Fi1dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
    FILENAME = 'D:\SalesData\SG2Fi2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'E:\SalesLog\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

E. Attaching a database

USE master;
GO
sp_detach_db Archive;
GO
CREATE DATABASE Archive
      ON (FILENAME = 'D:\SalesData\archdat1.mdf') 
      FOR ATTACH ;
GO

F. Creating a database snapshot

USE master;
GO
CREATE DATABASE sales_snapshot0600 ON
    ( NAME = SPri1_dat, FILENAME = 'D:\SalesData\SPri1dat_0600.ss'),
    ( NAME = SPri2_dat, FILENAME = 'D:\SalesData\SPri2dt_0600.ss'),
    ( NAME = SGrp1Fi1_dat, FILENAME = 'D:\SalesData\SG1Fi1dt_0600.ss'),
    ( NAME = SGrp1Fi2_dat, FILENAME = 'D:\SalesData\SG1Fi2dt_0600.ss'),
    ( NAME = SGrp2Fi1_dat, FILENAME = 'D:\SalesData\SG2Fi1dt_0600.ss'),
    ( NAME = SGrp2Fi2_dat, FILENAME = 'D:\SalesData\SG2Fi2dt_0600.ss')
AS SNAPSHOT OF Sales ;
GO

G. Creating a database and specifying a collation name and options

USE master;
GO
IF DB_ID (N'MyOptionsTest') IS NOT NULL
DROP DATABASE MyOptionsTest;
GO
CREATE DATABASE MyOptionsTest
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'MyOptionsTest';
GO

H. Attaching a full-text catalog that has been moved

USE master;
GO
--Detach the AdventureWorks database
sp_detach_db AdventureWorks;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'), 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO

I. Creating a database that specifies a row filegroup and two FILESTREAM filegroups

USE master;
GO
IF DB_ID (N'FileStreamDB') IS NOT NULL
DROP DATABASE FileStreamDB;
GO
-- Get the SQL Server data path.
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

 -- Execute the CREATE DATABASE statement. 
EXECUTE ('CREATE DATABASE FileStreamDB
ON PRIMARY 
    (
    NAME = FileStreamDB_data 
    ,FILENAME = ''' + @data_path + 'FileStreamDB_data.mdf''
    ,SIZE = 10MB
    ,MAXSIZE = 50MB
    ,FILEGROWTH = 15%
    ),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
    (
    NAME = FSPhotos
    ,FILENAME = ''C:\MyFSfolder\Photos''
-- SIZE, MAXSIZE, FILEGROWTH should not be specified here.
-- If they are specified an error will be raised.
    ),
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
    (
    NAME = FileStreamResumes
    ,FILENAME = ''C:\MyFSfolder\Resumes''
    ) 
LOG ON
    (
    NAME = FileStream_log
    ,FILENAME = ''' + @data_path + 'FileStreamDB_log.ldf''
    ,SIZE = 5MB
    ,MAXSIZE = 25MB
    ,FILEGROWTH = 5MB
    )'
);
GO

See Also

Reference

Concepts

Other Resources