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]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 ) GOB. 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 ) GOD. 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 mytestE. 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' ) GOF. 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 ) GOG. 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 ArchiveGOCREATE DATABASE Archive ON PRIMARY (FILENAME = 'c:\mssql7\data\archdat1.mdf') FOR ATTACH GOH. 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 masterGOCREATE DATABASE EmployeesON ( 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 ALTER DATABASE sp_removedbreplication DROP DATABASE sp_helpfile sp_attach_db sp_helpfilegroup sp_changedbowner sp_renamedb sp_detach_db sp_spaceused sp_helpdb Using Raw Partitions
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]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 )
GOB. 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 )
GOD. 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 mytestE. 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' )
GOF. 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 )
GOG. 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 ArchiveGOCREATE DATABASE Archive
ON PRIMARY (FILENAME = 'c:\mssql7\data\archdat1.mdf')
FOR ATTACH
GOH. 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 masterGOCREATE DATABASE EmployeesON ( 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
ALTER DATABASE sp_removedbreplication
DROP DATABASE sp_helpfile
sp_attach_db sp_helpfilegroup
sp_changedbowner sp_renamedb
sp_detach_db sp_spaceused
sp_helpdb Using Raw Partitions