SQL Server/T-SQL Tutorial/Database/Create Database

A CREATE DATABASE Example

a complete example of the CREATE DATABASE command, 
specifying three files and all the properties of each file:
CREATE DATABASE Archive
ON
PRIMARY
( NAME = Arch1,
FILENAME = "c:\archdat1.mdf",
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = "c:\archdat2.ndf",
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME =
    "c:\archlog1.ldf",
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)

A statement that attaches an existing database file

CREATE DATABASE Test_AP
    ON PRIMARY (FILENAME = 
        "C:\Program Files\Microsoft SQL Server\
MSSQL\Data\Test_AP_Data.mdf")
    FOR ATTACH

Attach a mdf file to database

CREATE DATABASE Database_1b
ON PRIMARY (FILENAME = "c:\Database_1a.mdf")
FOR ATTACH
GO
SELECT name, database_id FROM sys.databases
EXEC sp_helpdb @dbname = N"Database_1b"
GO

Create a database by indicating the mdf file

CREATE DATABASE MyDatabase
ON
(NAME = MyDatabase_dat,
    FILENAME = "c:\MyDatabase.mdf",
    SIZE = 2MB,
    MAXSIZE = 20,
    FILEGROWTH = 10%)
EXEC sp_helpdb MyDatabase
DROP DATABASE MyDatabase

Create a database by specifying the mdf file

4>    IF NOT EXISTS (SELECT "True" FROM INFORMATION_SCHEMA.SCHEMATA WHERE
5>       CATALOG_NAME = "NorthwindCreate")
6>    BEGIN
7>       CREATE DATABASE NorthwindCreate
8>       ON
9>       ( NAME = NorthwindCreate,
10>         FILENAME = "c:\NorthwindCreate.mdf" )
11>    END
12>    ELSE
13>    BEGIN
14>       PRINT "Database already exists. Skipping CREATE DATABASE Statement"
15>    END
16>    GO

create and drop database

CREATE DATABASE pubs
GO
CHECKPOINT
GO
USE pubs
GO
drop database pubs;
GO

Creates a database with explicit specifications for database and transaction log files.

6> CREATE DATABASE projects
7>       ON (NAME=projects_dat,
8>         FILENAME = "C:\DATA\projects.mdf",
9>         SIZE = 10,
10>         MAXSIZE = 100,
11>         FILEGROWTH = 5)
12>       LOG ON
13>       (NAME=projects_log,
14>         FILENAME = "C:\DATA\projects.ldf",
15>         SIZE = 40,
16>         MAXSIZE = 100,
17>         FILEGROWTH = 10)
18>
19> drop database projects;
20> GO

Creation of a Database

This statement has the general form:
      CREATE DATABASE db_name
        [ON [PRIMARY] file_spec1 {, file_spec2} ...]
        [LOG ON file_spec3 {, file_spec4 ...]
        [COLLATE collation_name]
      [FOR {ATTACH|ATTACH_REBUILD_LOG{]
db_name is the name of the database. 
The maximum size of a database name is 128 characters.
The maximum number of databases managed by a single SQL Server system is 32,767.
All databases in SQL Server are stored in files. 
These files can be explicitly specified by the system administrator or implicitly provided by the system. 
If the ON option exists in the CREATE DATABASE statement, all files containing the data of a database are explicitly specified.

If a database exists

if exists (select * from sysdatabases where name="pubs")
begin
  raiserror("Dropping existing pubs database ....",0,1)
  DROP database pubs
end
GO