Create Database in Micrsoft Sql Server 2008R2

In this blog we will talk about the way we can create database in Microsoft Sql Server. Mostly new Sql server DBA create database by right clicking on database tab and select create database statement. It takes time for them to know different
options available while creating database.

I have complied all the possible options avaible to consider while creating database. Have a look:


DROP DATABASE SALES;

--++++++++++++++++++++++++++++++++++++++++++++++
Create database sales on
(
 name=sales_dat,
 FILENAME='C:\SqlServerDbFiles\sales\sales.mdf',
     size = 10,
maxsize=20,
filegrowth=5
)
Log on
(
 name=sales_log,
 FILENAME='C:\SqlServerDbFiles\sales\sales.log',
 size=10,
maxsize=20,
FILEGROWTH = 5
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++
drop database archive;

Create Database Archive on
Primary
( name = Arch1,
  filename='C:\SqlServerDbFiles\sales\archiv1.mdf',
  size = 2mb,
  maxsize = 5Mb,
  filegrowth = 2mb
 ),
 (name = Arch2,
  filename='C:\SqlServerDbFiles\sales\archiv2.ndf',
  size = 2mb,
  maxsize=3mb,
  filegrowth=3mb
  )
  Log On
  (name = arch_log1,
   filename='C:\SqlServerDbFiles\sales\archiv_log1.ldf',
   size = 2mb,
   maxsize = 5mb,
   filegrowth = 2mb
   ),
   (name=arch_log2,
    filename='C:\SqlServerDbFiles\sales\archiv_log2.ldf',
    size = 2Mb,
    MaxSize =  3Mb,
    FileGrowth = 2Mb
    )
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Drop Database Sales;

Create Database Sales on
primary
(
    name=Spril1_dat,
    fileName='C:\SqlServerDbFiles\sales\Spril1dat.mdf',
    size = 2MB,
    MaxSize=5MB,
    FileGrowth = 2%
),
(
   Name=Spril2_dat,
   FileName='C:\SqlServerDbFiles\sales\Spril2dat.ndf',
   size = 3MB,
   Maxsize = 6Mb,
   FileGrowth=3
),
FileGroup SalesGroup1
(
    Name=SgGrpFi1_dat,
    FileName='C:\SqlServerDbFiles\sales\SgGrpFi1dat.ndf',
    Size = 3MB,
    MaxSize = 6MB,
    FileGrowth =  2
),
(
    Name=SgGrpFi2_dat,
    FileName='C:\SqlServerDbFiles\sales\SgGrpFi2dat.ndf',
    Size = 3MB,
    MaxSize = 6MB,
    FileGrowth =  2
),
FileGroup SalesGroup2
(
  name = SG2Fil1_dat,
  FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi1dat.ndf',
  Size = 4MB,
  MaxSize =  8MB,
  FileGrowth = 3%   
),
(
  name = SG2Fil2_dat,
  FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi2dat.ndf',
  Size = 4MB,
  MaxSize =  8MB,
  FileGrowth = 3%   
)   
Log ON
 (
  name = Sales_Log,
  FileName='C:\SqlServerDbFiles\sales\Sales_Log.log',
  Size = 4MB,
  MaxSize =  8MB,
  FileGrowth = 3%   
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--attaching a database
Create Database Archive
on (Filename='C:\SqlServerDbFiles\archive\Archiv1.mdf')
for Attach

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--SnapShot Database
Create Database ArchiveSnpshot On
(NAME=Spri1_dat,filename='C:\SqlServerDbFiles\archive\Archiv1.mdf'),
(name=Spri2_dat,FileName='C:\SqlServerDbFiles\archive\Archiv2.mdf'),
(Name=spri3_dat,FileName='C:\SqlServerDbFiles\archive\Archiv1.mdf')
As SnapShot Of Sales)
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE DATABASE Sales_Colla
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'Sales_Colla';
GO
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE DATABASE AdventureWorks ON
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf'),
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+Specifies row file group and FileStream
CREATE DATABASE FileStreamDB
ON PRIMARY
    (
    NAME = FileStreamDB_data
    ,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_data.mdf'
    ,SIZE = 10MB
    ,MAXSIZE = 50MB
    ,FILEGROWTH = 15%
    ),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
    (
    NAME = FSPhotos
    ,FILENAME = 'C:\MyFSfolder\Photos'
-- SIZE, MAXSIZE, FILEGROWTH should not be specified here.
-- If they are specified an error will be raised.
    ),
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
    (
    NAME = FileStreamResumes
    ,FILENAME = 'C:\MyFSfolder\Resumes'
    )
LOG ON
    (
    NAME = FileStream_log
    ,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_log.ldf'
    ,SIZE = 5MB
    ,MAXSIZE = 25MB
    ,FILEGROWTH = 5MB
    )


Any information,comment,suggestion would be welcome.


thanks