CREATE DATABASE
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 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 ]
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 a maximum of 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-separated 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-separated list of <filegroup> items defining user filegroups and their files.
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-separated 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 required in SQL Server version 7.0 because the RESTORE statement can recreate a database as part of the restore operation.
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 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.
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.
collation_name
Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the SQL Server instance.
For more information about the Windows and SQL collation names, see COLLATE.
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.
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 an instance of SQL Server. os_file_name cannot specify a directory in a compressed file system.
If the file is 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 kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. 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 kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. 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 almost 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, GB, TB, or percent (%). 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 percent 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:
- SQL Server uses a copy of the model database to initialize the database and its meta data.
- 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.
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.
| File type | File name extension |
|---|---|
| Primary data file | .mdf |
| Secondary data file | .ndf |
| Transaction log file | .ldf |
Note The master database should be backed up when a user database is created.
Fractions cannot be specified in the SIZE, MAXSIZE, and FILEGROWTH parameters. To specify a fraction of a megabyte in SIZE parameters, convert to kilobytes by multiplying the number by 1,024. For example, specify 1,536 KB instead of 1.5 MB (1.5 multiplied by 1,024 equals 1,536).
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 with sp_changedbowner.
To display a report on a database, or on all the databases for an instance of 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 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. The 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 an instance of SQL Server.
Examples
A. Create a database that specifies 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) becomes the primary file. Because neither MB or KB is 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:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\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 data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files.
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql server\mssql\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 becomes the primary file, and a 1-MB transaction log file is automatically created. Because neither MB or KB is specified in the SIZE parameter for the primary file, the primary file is allocated in megabytes. Because there is no <filespec> for the transaction log file, the transaction log file has no MAXSIZE and can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products
ON
( NAME = prods_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\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 disk space.
CREATE DATABASE mytest
E. Create a database without specifying SIZE
This example creates a database named products2. The file prods2_dat becomes 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 disk space.
USE master
GO
CREATE DATABASE Products2
ON
( NAME = prods2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\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 percent.
- 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:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
G. Attach a database
Example B creates a database named Archive with the following physical files:
c:\program files\microsoft sql server\mssql\data\archdat1.mdf
c:\program files\microsoft sql server\mssql\data\archdat2.ndf
c:\program files\microsoft sql server\mssql\data\archdat3.ndf
c:\program files\microsoft sql server\mssql\data\archlog1.ldf
c:\program files\microsoft sql server\mssql\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:\program files\microsoft sql server\mssql\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
I. Use mounted drives
This example creates a database called Employees using mounted drives pointing to raw partitions. This feature is available only in Microsoft® Windows® 2000 Server. The mounted drives and raw partitions must exist when the statement is executed, and only one file can go on each raw partition. When creating a database file on a mounted drive, a trailing backslash (\) must end the drive path.
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'd:\sample data dir\',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'd:\sample log dir\',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO