CREATE DATABASE

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

Syntax
Create database
      CREATE DATABASE database 
         [ON [PRIMARY ]
             [
               filespec [,...n ] 
               [, filegroup [,...n] ] 
                  [LOG ON {filespec [,...n] } ] 
             ] 
            [COLLATE collation ]
               [WITH external_access_option ]
         ] [;]

Attach a database
      CREATE DATABASE database 
         ON filespec [ ,...n ] 
            FOR { ATTACH [ WITH service_broker_option ]
                | ATTACH_REBUILD_LOG } [;]

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

Key:
   filespec: 
      (
          NAME = logical_file_name ,
          FILENAME = 'os_file_name' 
              [ , 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 [ DEFAULT ]
         filespec [,...n]
 
   external_access_option:
      DB_CHAINING {ON | OFF}
      TRUSTWORTHY {ON | OFF}

   service_broker_option:
      ENABLE_BROKER
      NEW_BROKER
      ERROR_BROKER_CONVERSATIONS

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

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

Equivalent Oracle command: None, delete the datafiles and stop the background processes (OraTab/Services)