CREATE DATABASE

Create a new database, create database storage files, create snapshot, attach db to data files.

Syntax

Create database

      CREATE DATABASE database
         [ CONTAINMENT = { NONE | PARTIAL } ]
            [ON [PRIMARY ]
               [
                 filespec [,...n ] 
                 [, filegroup [,...n] ] 
                    [LOG ON {filespec [,...n] } ] 
               ] 
            [COLLATE collation ]
               [WITH option ]
         ] [;]

Key:

   option ::=
   {
      FILESTREAM ( filestream_option [,...n ] )
    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
    | NESTED_TRIGGERS = { OFF | ON }
    | TRANSFORM_NOISE_WORDS = { OFF | ON }
    | TWO_DIGIT_YEAR_CUTOFF = two_digit_year_cutoff
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
    | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='path-to-directory-on-a-DAX-volume' )
    | LEDGER = {ON | OFF }
   }

   filestream_option ::=
   {
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name'
   }

   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 ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
       filespec [ ,...n ]
   }


Attach a database

      CREATE DATABASE database_name
         ON filespec [ ,...n ]
            FOR { { ATTACH [ WITH attach_database_option [ , ...n ] ] }
            | ATTACH_REBUILD_LOG }
      [;]

Key:

   attach_database_option ::=
   {
      service_broker_option
    | RESTRICTED_USER
    | FILESTREAM ( DIRECTORY_NAME = { 'directory_name' | NULL } )
   }

   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
[;]

database_name
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.

PRIMARY - Identifies the (at most one) filespec list which defines the primary file.

COLLATE collation_name
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.

CONTAINMENT = { NONE | PARTIAL }
Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.

LOG ON - The disk files used to store the database log

FOR ATTACH - Create database by attaching an existing set of OS files.

FOR ATTACH_REBUILD_LOG - Create database by attaching an existing set of OS files and Rebuild any missing transaction log files.

DB_CHAINING - Allow cross-database ownership chaining.

TRUSTWORTHY - Allow database modules to access resources outside the database while using an impersonation context.

ENABLE_BROKER - Enable the Service Broker for the database.

NEW_BROKER - Create a new service_broker_guid, end all conversation endpoints with clean up.

ERROR_BROKER_CONVERSATIONS - End all conversations, re-enable when operation is completed.

Examples

CREATE DATABASE SS64
ON 
( NAME = SS64_dat,
    FILENAME = 'E:\DATA\ss64database\SS64_data.mdf',
    SIZE = 100MB,
    MAXSIZE = 150MB,
    FILEGROWTH = 25MB )
LOG ON
( NAME = SS64_log,
    FILENAME = 'E:\DATA\ss64database\SS64_log.ldf',
    SIZE = 50MB,
    MAXSIZE = 75MB,
    FILEGROWTH = 15MB )'
);
GO

-- Verify the database files
SELECT name, size, size*1.0/128 AS [Size in MBs]
FROM sys.master_files
WHERE name = N'SS64';
GO

“All things are created twice. There's a mental or first creation, and a physical or second creation of all things. You have to make sure that the blueprint, the first creation, is really what you want, that you've thought everything through. Then you put it into bricks and mortar” ~ Stephen Covey

Related commands

ALTER DATABASE
DROP DATABASE
sys.databases
sys.master_files