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 in Microsoft® SQL Server™ Backward Compatibility Details.

Syntax

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

< filespec > ::=

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

< filegroup > ::=

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

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 meta data.
   
   
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, to be included in all 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 ALTER DATABASE to change the options for the model database, these option settings are in effect for 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.

.