CREATE DATABASE (T-SQL)

Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.

Note For more information about backward compatibility with DISK INIT, see Devices (Level 3) in SQL Server Backward Compatibility Details.

Syntax

CREATE DATABASE database_name
[ ON [PRIMARY]
        [ <filespec> [,...n] ]
        [, <filegroup> [,...n] ]
]
[ LOG ON { <filespec> [,...n]} ]
[ FOR LOAD | FOR ATTACH ]

<filespec> ::=

  ( [ NAME = logical_file_name, ]
  FILENAME = 'os_file_name'
  [, SIZE = size]
  [, MAXSIZE = { max_size | UNLIMITED } ]
  [, FILEGROWTH = growth_increment] ) [,...n]

<filegroup> ::=

FILEGROUP filegroup_name <filespec> [,...n]

Arguments

database_name

Is the name of the new database. Database names must be unique within a server and conform to the rules for identifiers. database_name can be up to 128 characters, unless no logical name is specified for the log. If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.

ON

Specifies that the disk files used to store the data portions of the database (data files) are defined explicitly. The keyword is followed by a comma-delimited list of <filespec> items defining the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-delimited list of <filegroup> items defining user filegroups and their files.

PRIMARY

Specifies that the associated <filespec> list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first <filespec> entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

n

Is a placeholder indicating that multiple files can be specified for the new database.

LOG ON

Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-delimited list of <filespec> items defining the log files. If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.

FOR LOAD

This clause is supported for compatibility with earlier versions of Microsoft SQL Server. The database is created with the dbo use only database option turned on, and the status is set to loading. This is not needed in SQL Server version 7.0 because the RESTORE statement can re-create a database as part of the restore operation.

If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.

FOR ATTACH

Specifies that a database is attached from an existing set of operating system files. There must be a <filespec> entry specifying the first primary file. The only other <filespec> entries needed 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. The database being attached must have been created using the same code page and sort order as SQL Server. Use the sp_attach_db system stored procedure instead of using CREATE DATABASE FOR ATTACH directly. Use CREATE DATABASE FOR ATTACH only when you must specify more than 16 <filespec> items.

NAME

Specifies the logical name for the file defined by the <filespec>. The NAME parameter is not required when FOR ATTACH is specified.

logical_file_name

Is the name used to reference the file in any Transact-SQL statements executed after the database is created. logical_file_name must be unique in the database and conform to the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.

FILENAME

Specifies the operating-system file name for the file defined by the <filespec>.

'os_file_name'

Is the path and file name used by the operating system when it creates the physical file defined by the <filespec>. The path in os_file_name must specify a directory on the server in which SQL Server is installed. os_file_name cannot specify a directory in a compressed file system.

If the file is being created on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be created on each raw partition. Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not needed when os_file_name specifies a raw partition.

SIZE

Specifies the size of the file defined in the <filespec>. When a SIZE parameter is not supplied in the <filespec> for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file 1 MB.

size

Is the initial size of the file defined in the <filespec>. The KB and MB suffixes can be used to specify kilobytes or megabytes, the default is MB. Specify a whole number; do not include a decimal. The minimum value for size is
512 KB. If size is not specified, the default is 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

MAXSIZE

Specifies the maximum size to which the file defined in the <filespec> can grow.

max_size

Is the maximum size to which the file defined in the <filespec> can grow. The KB and MB suffixes can be used to specify kilobytes or megabytes, 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.

---

Note The Microsoft Windows NT® S/B system log warns the SQL Server system administrator if a disk is about to become full.

---

UNLIMITED

Specifies that the file defined in the <filespec> grows until the disk is full.

FILEGROWTH

Specifies the growth increment of the file defined in the <filespec>. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

growth_increment

Is the amount of space added to the file each time new space is needed. Specify a whole number; do not include a decimal. A value of 0 indicates no growth. The value can be specified in MB, KB, or %. 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. If FILEGROWTH is not specified, the default value is 10% and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.

Remarks

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 in two steps:

1. SQL Server uses a copy of the model database to initialize the database and its metadata.
2. SQL Server then fills the rest of the database with empty pages, except for pages that have internal data recording how the space is used in the database.

Any user-defined objects in the model database are therefore copied to all newly created databases. You can add to the model database any objects (such as tables, views, stored procedures, data types, and so on) that you want to have in all your databases.

Each new database inherits the database option settings from the model database (unless FOR ATTACH is specified). For example, the database option select into/bulkcopy is set to OFF in model and any new databases you create. If you use sp_dboption to change the options for the model database, these option settings will be in effect for any new databases you create. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.

A maximum of 32,767 databases can be specified on a server.

There are three types of files used to store a database:

• The primary file contains the startup information for the database. The primary file is also used to store data. Every database has one primary file.
• Secondary files hold all of the data that does not fit in the primary data file. Databases need not have any secondary data files if the primary file is large enough to hold all of the data in the database. Other databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.
• Transaction log files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a transaction log file is 512 KB.

Every database has at least two files, a primary file and a transaction log file.

Although os_file_name can be any valid operating-system file name, the name more clearly reflects the purpose of the file if you use the following recommended extensions.

Note The master database should be backed up after a user database is created.

Fractions cannot be specified in the SIZE, MAXSIZE, and FILEGROWTH parameters. To specify a fraction of a megabyte in the size parameters, convert to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 times 1024 equals 1536).

When a simple CREATE DATABASE database_name statement is specified with no additional parameters, the database is made the same size as the model database.

All databases have at least a primary filegroup. All system tables are allocated in the primary filegroup. A database can also have user-defined filegroups. If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup. The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup. When a database is first created the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE:

ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name DEFAULT

Each database has an owner who has the ability to perform special activities in the database. The owner is the user who creates the database. The database owner can be changed through sp_changedbowner.

To display a report on a database or on all the databases on a server running SQL Server, execute sp_helpdb. For a report on the space used in a database, use sp_spaceused. For a report on the filegroups in a database use sp_helpfilegroup, and use sp_helpfile for a report of the files in a database.

Earlier versions of SQL Server used DISK INIT statements to create the files for a database before the CREATE DATABASE statement was executed. For backward compatibility with earlier versions of SQL Server, the CREATE DATABASE statement can also create a new database on files or devices that were created with the DISK INIT statement. For more information, see SQL Server Backward Compatibility Details.

Permissions

CREATE DATABASE permission defaults to members of the sysadmin and dbcreator fixed server roles. Members of the sysadmin and securityadmin fixed server roles can grant CREATE DATABASE permissions to other logins. Members of the sysadmin and dbcreator fixed server role can add other logins to the dbcreator role. CREATE DATABASE permission must be explicitly granted; it is not granted by the GRANT ALL statement.

CREATE DATABASE permission is usually limited to a few logins to maintain control over disk usage on the computer running SQL Server.

Examples

A. Create a database specifying the data and transaction log files

This example creates a database called Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) defaults to being the primary file. Because neither MB or KB are specified in the SIZE parameter for the Sales_dat file, it defaults to MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

USE master

GO

CREATE DATABASE Sales

ON

( NAME = Sales_dat,

  FILENAME = 'c:\mssql7\data\saledat.mdf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 )

LOG ON

( NAME = 'Sales_log',

  FILENAME = 'c:\mssql7\data\salelog.ldf',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB )

GO

B. Create a database specifying multiple data and transaction log files

This example creates a database called Archive with three 100-MB data files and two 100-MB transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. The transaction log files are specified following the LOG ON keywords. Note the extensions used for the files in the FILENAME option: .mdf is used for primary files, .ndf is used for the secondary files, and .ldf is used for transaction log files.

USE master

GO

CREATE DATABASE Archive

ON

PRIMARY ( NAME = Arch1,

          FILENAME = 'c:\mssql7\data\archdat1.mdf',

          SIZE = 100MB,

          MAXSIZE = 200,

          FILEGROWTH = 20),

( NAME = Arch2,

  FILENAME = 'c:\mssql7\data\archdat2.ndf',

  SIZE = 100MB,

  MAXSIZE = 200,

  FILEGROWTH = 20),

( NAME = Arch3,

  FILENAME = 'c:\mssql7\data\archdat3.ndf',

  SIZE = 100MB,

  MAXSIZE = 200,

  FILEGROWTH = 20)

LOG ON

( NAME = Archlog1,

  FILENAME = 'c:\mssql7\data\archlog1.ldf',

  SIZE = 100MB,

  MAXSIZE = 200,

  FILEGROWTH = 20),

( NAME = Archlog2,

  FILENAME = 'c:\mssql7\data\archlog2.ldf',

  SIZE = 100MB,

  MAXSIZE = 200,

  FILEGROWTH = 20)

GO

C. Create a simple database

This example creates a database called Products and specifies a single file. The file specified defaults to being the primary file, and a 1-MB transaction log file is automatically created. Because neither MB or KB are specified in the SIZE parameter for the primary file, the primary file is allocated in megabytes. Because there is so <filespec> for the transaction log file, the transaction log file has no MAXSIZE and can grow to fill all available space on the disk.

USE master

GO

CREATE DATABASE Products

ON

( NAME = prods_dat,

  FILENAME = 'c:\mssql7\data\prods.mdf',

  SIZE = 4,

  MAXSIZE = 10,

  FILEGROWTH = 1 )

GO

D. Create a database without specifying files

This example creates a database named mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is the size of the model database transaction log file. Because MAXSIZE is not specified, the files can grow to fill all available space on the disk.

CREATE DATABASE mytest

E. Create a database without specifying SIZE

This example creates a database named products2. The file prods2_dat defaults to being the primary file with a size equal to the size of the primary file in the model database. The transaction log file is created automatically and is 25 percent of the size of the primary file or 512 KB, whichever is larger. Because MAXSIZE is not specified, the files can grow to fill all available space on the disk.

USE master

GO

CREATE DATABASE Products2

ON

( NAME = prods2_dat,

  FILENAME = 'c:\mssql7\data\prods2.mdf' )

GO

F. Create a database with filegroups

This example creates a database named sales with three filegroups:

• The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15%.
• A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
• A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

CREATE DATABASE Sales

ON PRIMARY

( NAME = SPri1_dat,

  FILENAME = 'c:\mssql7\data\SPri1dat.mdf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 15% ),

( NAME = SPri2_dat,

  FILENAME = 'c:\mssql7\data\SPri2dt.ndf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 15% ),

FILEGROUP SalesGroup1

( NAME = SGrp1Fi1_dat,

  FILENAME = 'c:\mssql7\data\SG1Fi1dt.ndf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

( NAME = SGrp1Fi2_dat,

  FILENAME = 'c:\mssql7\data\SG1Fi2dt.ndf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

FILEGROUP SalesGroup2

( NAME = SGrp2Fi1_dat,

  FILENAME = 'c:\mssql7\data\SG2Fi1dt.ndf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

( NAME = SGrp2Fi2_dat,

  FILENAME = 'c:\mssql7\data\SG2Fi2dt.ndf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 )

LOG ON

( NAME = 'Sales_log',

  FILENAME = 'c:\mssql7\data\salelog.ldf',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB )

GO

G. Attach a database

Example B created a database named Archive with the following physical files:

c:\mssql7\data\archdat1.mdf

c:\mssql7\data\archdat2.ndf

c:\mssql7\data\archdat3.ndf

c:\mssql7\data\archlog1.ldf

c:\mssql7\data\archlog2.ldf

The database can be detached using the sp_detach_db stored procedure, and then reattached using CREATE DATABASE with the FOR ATTACH clause:

sp_detach_db Archive

GO

CREATE DATABASE Archive

ON PRIMARY (FILENAME = 'c:\mssql7\data\archdat1.mdf')

FOR ATTACH

GO

H. Use raw partitions

This example creates a database called Employees using raw partitions. The raw partitions must exist when the statement is executed, and only one file can go on each raw partition.

USE master

GO

CREATE DATABASE Employees

ON

( NAME = Empl_dat,

  FILENAME = 'f:',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 )

LOG ON

( NAME = 'Sales_log',

  FILENAME = 'g:',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB )

GO

See Also