MSDN Documentation > SQL Server > Transact-SQL > Statements > CREATE DATABASE

CREATE DATABASE (Transact-SQL)

Creates a new database in SQL Server.

Syntax

CREATE DATABASE database_name [ ON [ PRIMARY [ , [ ,...n ] ] ] [ , [ ,...n ] ] ] [ LOG ON { [ ,...n ] } ] [ COLLATE collation_name ] [ WITH

Where:

Parameter Description
database_name The name of the database to create. Database names must be unique within an instance of SQL Server and conform to the rules for identifiers.
ON PRIMARY Specifies that the following file specifications define the primary filegroup. If ON PRIMARY is not specified, the first file specified becomes the primary file.
<file_spec> Specifies the physical files that make up the database.
  • NAME = logical_file_name: The name used in the SQL Server metadata for the file.
  • FILENAME = 'os_file_name': The operating system file path where the file is stored.
  • SIZE = size [ KB | MB | GB | TB ]: The initial size of the file. Defaults to the size of the model database.
  • MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED }: The maximum size to which the file can grow.
  • FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ]: The amount of disk space automatically added to the file when it runs out of space.
<filegroup> Specifies the definition of a filegroup.
  • FILEGROUP filegroup_name [ DEFAULT ] { <file_spec> [ ,...n ] }: Defines a filegroup and its files. DEFAULT designates this filegroup as the default for new objects.
LOG ON Specifies that the following file specifications define the transaction log files.
COLLATE collation_name Specifies the collation for the database. If omitted, the collation of the instance is used.
WITH <option> Specifies database options. Common options include:
  • RECOVERY { FULL | BULK_LOGGED | SIMPLE }: Sets the recovery model.
  • FILE_STREAM ( DIRECTORY_NAME = 'directory_name' ): Enables FileStream.
  • CONTAINMENT { NONE | PARTIAL }: Sets database containment.

Description

The CREATE DATABASE statement is a fundamental T-SQL command used to initialize a new SQL Server database. It defines the database name, its physical files (data and log), their sizes, growth properties, and optionally sets various database options like recovery model, collation, and FileStream settings.

Each database consists of at least one data file and one transaction log file. Data files store the actual database objects (tables, indexes, etc.) and data. Transaction log files record all modifications made to the database, enabling recovery in case of system failures.

The model database serves as a template for new databases. When you create a new database, SQL Server copies the structure of the model database and then applies the specified file sizes and options. Any changes made to the model database will affect all subsequently created databases.

Permissions

Requires CREATE DATABASE permission or CREATE ANY DATABASE permission in the master database.

Examples

Example 1: Basic Database Creation

This example creates a simple database named MyNewDatabase with a primary data file and a transaction log file.

CREATE DATABASE MyNewDatabase ON PRIMARY ( NAME = MyNewDatabase_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyNewDatabase_Data.mdf', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB ) LOG ON ( NAME = MyNewDatabase_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyNewDatabase_Log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );

Example 2: Database with Multiple Filegroups

This example creates a database with a primary filegroup and a secondary filegroup named SecondaryFG.

CREATE DATABASE SalesDB ON PRIMARY ( NAME = SalesDB_Primary, FILENAME = 'D:\Data\SalesDB_Primary.mdf', SIZE = 100MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), FILEGROUP SecondaryFG ( NAME = SalesDB_Secondary, FILENAME = 'E:\Data\SalesDB_Secondary.ndf', SIZE = 50MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) LOG ON ( NAME = SalesDB_Log, FILENAME = 'F:\Logs\SalesDB_Log.ldf', SIZE = 20MB, MAXSIZE = 100MB, FILEGROWTH = 10MB );

Example 3: Database with Specific Collation and Recovery Model

This example creates a database with a specific collation and sets the recovery model to SIMPLE.

CREATE DATABASE UserDB COLLATE SQL_Latin1_General_CP1_CI_AS WITH RECOVERY SIMPLE;

Notes

Note: The actual file paths (e.g., C:\Program Files\...) in the examples should be replaced with valid paths on your SQL Server instance. Ensure the SQL Server service account has write permissions to these directories.

Tip: It is generally recommended to place data files and log files on separate physical drives for better performance and recovery capabilities.

Warning: Using UNLIMITED for MAXSIZE can lead to disk space exhaustion if not monitored carefully. It's often better to set a reasonable maximum size.

See Also