CREATE DATABASE

CREATE DATABASE

Creates a new database.

Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.

For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

 

Overview

In SQL Server, this statement creates a new database and the files used and their filegroups. It can also be used to create a database snapshot, or attach database files to create a database from the detached files of another database.

 

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 changed by using the Server Properties (Database Settings Page) in Management Studio. Changing the default path requires restarting SQL Server.

CONTAINMENT = { NONE | PARTIAL }

Applies to: SQL Server 2012 (11.x) and later

 

Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.

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 Database Files and Filegroups.

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, which 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. This file is placed in the default log-file location. For information about this location, see View or Change the Default Locations for Data and Log Files - SSMS.

LOG ON cannot be specified on a database snapshot.

 

Create a database using T SQL on a specified location

How to create a database using T SQL script on a specified location? Let's say, I want to create a SQL server database on D:\temp\dbFolder. How to do this?

 

When you create the new database you specify the location. For example:

USE [master]
GO

    CREATE DATABASE [AdventureWorks] ON  PRIMARY 
    ( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
     LOG ON 
    ( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
    GO

 

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ChuckTest')
    CREATE DATABASE ChuckTest
    ON PRIMARY
           (
               NAME = N'ChuckTest_Data',
               FILENAME = N'D:\MSSQL\SQL2014\DATA\ChuckTest_Data.mdf',
               SIZE = 167872KB,
               MAXSIZE = UNLIMITED,
               FILEGROWTH = 16384KB
           )
    LOG ON
        (
            NAME = N'ChuckTest_Log',
            FILENAME = N'D:\MSSQL\SQL2014\DATA\ChuckTest_Log.ldf',
            SIZE = 2048KB,
            MAXSIZE = 2048GB,
            FILEGROWTH = 16384KB
        );
GO