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

A CREATE DATABASE Example

   <source lang="sql">

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)</source>

A statement that attaches an existing database file

   <source lang="sql">

CREATE DATABASE Test_AP

   ON PRIMARY (FILENAME = 
       "C:\Program Files\Microsoft SQL Server\

MSSQL\Data\Test_AP_Data.mdf")

   FOR ATTACH</source>
   
  

Attach a mdf file to database

   <source lang="sql">

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</source>

Create a database by indicating the mdf file

   <source lang="sql">

CREATE DATABASE MyDatabase ON (NAME = MyDatabase_dat,

   FILENAME = "c:\MyDatabase.mdf",
   SIZE = 2MB,
   MAXSIZE = 20,
   FILEGROWTH = 10%)

EXEC sp_helpdb MyDatabase DROP DATABASE MyDatabase</source>

Create a database by specifying the mdf file

   <source lang="sql">

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</source>

create and drop database

   <source lang="sql">

CREATE DATABASE pubs GO CHECKPOINT GO USE pubs GO drop database pubs; GO</source>

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

   <source lang="sql">

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</source>

Creation of a Database

   <source lang="sql">

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.</source>

If a database exists

   <source lang="sql">

if exists (select * from sysdatabases where name="pubs") begin

 raiserror("Dropping existing pubs database ....",0,1)
 DROP database pubs

end GO</source>