CREATE DATABASE:Creates a new database and the files used to store the database, a database snapshot, or attaches a database from the detached files of a previously created database.
CREATE DATABASE testdb
( NAME = testdb_dat,
FILENAME = 'E:\Databases\testdb.mdf' )
( NAME = testdb_log,
FILENAME = 'G:\Logs\testdb_log.ldf')
Creating a database without specifying files
The following example creates the database 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 set to the larger of these values: 512KB or 25% the size of the primary data file. Because MAXSIZE is not specified, the files can grow to fill all available disk space. This example also demonstrates how to drop the database named mytest if it exists, before creating the mytest database.
USE master; GO CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) ; GO
Attaching a databaseThe following example detaches the database Archive created in example D, and then attaches it by using the FOR ATTACH clause. Archive was defined to have multiple data and log files. However, because the location of the files has not changed since they were created, only the primary file has to be specified in the FOR ATTACH clause. Beginning with SQL Server 2005, any full-text files that are part of the database that is being attached will be attached with the database.