syntaxhelp.com Your online cheat sheet!

Creating Database with default options

--Default size for mdf and ldf file is size of the model database
--Default MaxSize is unlimited as per the disk size for datafile and 2 TB for log file
--Default FileGrowth is 1 MB for data file and 10% for log file
CREATE DATABASE SyntaxTest
GO
--Check the default options 
SELECT file_id,name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id name	       	 size	max_size	growth
----------------------------------------------------- 
--1		SyntaxTest	  	 152	-1	        128	
--2     SyntaxTest_log   63	    268435456	10

Creating Database that specifies Data and Log file

--Name and FileName are logical file name and physical file name respectively
--A database can have only one primary file
--Default size is in MB
--FileGrowth is the amount of space added to the file every time new space is required
--When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs
CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat,
    FILENAME = 'C:\SyntaxTest_dat.mdf',
    SIZE = 10,
    MAXSIZE = 100,
    FILEGROWTH = 15% )
LOG ON
(	NAME = SyntaxTest_log,
    FILENAME = 'C:\SyntaxTest_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB )
GO
--Check the details
SELECT file_id,name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name			size	max_size	growth	
--------------------------------------------------------
--1		SyntaxTest_dat		1280	12800		15	
--2		SyntaxTest_log		640		6400		640	
   
--Unlimited MaxSize allows to grow the data file as per the disk size and 2 TB for log file
CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat,
    FILENAME = 'C:\SyntaxTest_dat.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 15% )
LOG ON
(	NAME = SyntaxTest_log,
    FILENAME = 'C:\SyntaxTest_log.ldf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5MB )
GO
--Check the details    
SELECT file_id,name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name			size	max_size	growth	
--------------------------------------------------------
--1		SyntaxTest_dat		1280	-1		    15	
--2		SyntaxTest_log		640		268435456	640	

Creating Database with FileGroups

--A maximum of 32,767 files and 32,767 filegroups can be specified for each database
CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat,
    FILENAME = 'c:\SyntaxTest_dat.mdf',
    SIZE = 10,
    MAXSIZE = 100,
    FILEGROWTH = 15% ),
FILEGROUP SyntaxTest_FG1
(	NAME = SyntaxTest_FG1_dat1,
    FILENAME = 'c:\SyntaxTest_FG1_dat1.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
(	NAME = SyntaxTest_FG1_dat2,
    FILENAME = 'd:\SyntaxTest_FG1_dat2.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
(	NAME = SyntaxTest_log,
    FILENAME = 'c:\SyntaxTest_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )
GO
--Check the details
SELECT file_id,name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name		  size	max_size	growth
--------------------------------------------------------
--1		SyntaxTest_dat		1280	12800		15
--2		SyntaxTest_log		640		3200		640
--3		SyntaxTest_FG1_dat  11280	6400		640
--4		SyntaxTest_FG1_dat2	1280	6400		640

Creating Database with Primary FileGroup

--If LOG ON is not specified, one log file is automatically created that has a size that is 25 percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger
CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat,
    FILENAME = 'C:\SyntaxTest_dat.mdf',
    SIZE = 10,
    MAXSIZE = 100,
    FILEGROWTH = 15% )
GO  
--Check the details
SELECT file_id,name,physical_name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name	physical_name	         size	max_size	growth	
------------------------------------------------------------------------
--1		SyntaxTest_dat	C:\SyntaxTest_dat.MDF   1280	12800		15
--2		SyntaxTest_log  C:\SyntaxTest_log.LDF 	320		268435456	10

--Creating two files in Primary FileGroup
CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat1,
    FILENAME = 'C:\SyntaxTest_dat1.mdf',
    SIZE = 100,
    MAXSIZE = 100,
    FILEGROWTH = 15% ),
(
    NAME = SyntaxTest_dat2,
    FILENAME = 'C:\SyntaxTest_dat2.mdf',
    SIZE = 50,
    MAXSIZE = 100,
    FILEGROWTH = 15%)
GO   
--Check the details    
SELECT file_id,name,physical_name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name			physical_name		size	max_size	growth	
----------------------------------------------------------------------------
--1		SyntaxTest_dat1	C:\SyntaxTest_dat1.mdf	12800	12800		15
--2		SyntaxTest_log	C:\SyntaxTest_log.LDF	4800	268435456	10
--3		SyntaxTest_dat2	C:\SyntaxTest_dat2.mdf	6400	12800		15

Creating Database with Multiple Files in Log FileGroup

CREATE DATABASE SyntaxTest  
ON PRIMARY
(	NAME = SyntaxTest_dat,
    FILENAME = 'C:\SyntaxTest_dat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% )
LOG ON
(	NAME = SyntaxTest_log1,
    FILENAME = 'C:\SyntaxTest_log1.ldf',
    SIZE = 5MB,
    MAXSIZE = 60,
    FILEGROWTH = 5MB ),
(	NAME = SyntaxTest_log2,
    FILENAME = 'C:\SyntaxTest_log2.ldf',
    SIZE = 5MB,
    MAXSIZE = 10,
    FILEGROWTH = 10MB )
GO 
--Check the details    
SELECT file_id,name,size,max_size,growth	
FROM master.sys.master_files
where database_id = db_id('SyntaxTest')

--Output
--file_id	name		size	max_size	growth	
-----------------------------------------------------
--1		SyntaxTest_dat	1280	6400		15
--2		SyntaxTest_log1	640		7680		640
--3		SyntaxTest_log2	640		1280		1280

Creating Database with Collation

--Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the instance of SQL Server.
CREATE DATABASE SyntaxTest
COLLATE French_CI_AI
GO
--Check the details
SELECT name, collation_name
FROM sys.databases
WHERE name = N'SyntaxTest';

--Output
--name		   collation_name
----------- ---------------------
--SyntaxTest	French_CI_AI

CREATE DATABASE SyntaxTest
GO
--Check the details
SELECT name, collation_name
FROM sys.databases
WHERE name = N'SyntaxTest';

--Output
--name		     collation_name
------------- ----------------------------
--SyntaxTest	SQL_Latin1_General_CP1_CI_AS

Creating Database with Trustworthy and DB_CHAINING

--When DB_CHAINING is ON, the database can be the source or target of a cross-database ownership chain. 
--When TRUSTWORTHY is ON, database modules that use an impersonation context can access resources outside the database. 
CREATE DATABASE SyntaxTest
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Check the details
SELECT name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'SyntaxTest';

Attaching Database

sp_detach_db SyntaxTest;
GO
--Automatically takes the other files, once the primary file path is provided, provided if the other files path are not changed
CREATE DATABASE SyntaxTest
ON (FILENAME = 'C:\SyntaxTest_dat.mdf')
FOR ATTACH 
GO

--Specify all the filespec if the file is moved from the original location
CREATE DATABASE SyntaxTest
ON (FILENAME = 'C:\SyntaxTest_dat.mdf'),
   (FILENAME = 'c:\divya\SyntaxTest_FG1_dat1.ndf')
FOR ATTACH;

Attaching Database with Service Broker Option

sp_detach_db SyntaxTest;
GO
--Service Broker options can only be specified when the FOR ATTACH clause is used
--ENABLE_BROKER Specifies that Service Broker is enabled for the specified database
CREATE DATABASE SyntaxTest
ON (FILENAME = 'C:\SyntaxTest_dat.mdf')
FOR ATTACH 
WITH ENABLE_BROKER;
GO
--Check the details
SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = N'SyntaxTest';

--Creating a new service_broker_guid for the restored database
CREATE DATABASE SyntaxTest
ON (FILENAME = 'C:\SyntaxTest_dat.mdf')
FOR ATTACH 
WITH NEW_BROKER;
GO
--Check the details
SELECT name, service_broker_guid
FROM sys.databases
WHERE name = N'SyntaxTest';

Attaching Database and Rebuilding log

sp_detach_db SyntaxTest;
GO
--Rebuilds log file if one or more log files are not available
CREATE DATABASE SyntaxTest
ON (FILENAME = 'C:\SyntaxTest_dat.mdf')
FOR ATTACH_REBUILD_LOG;
GO
-- Output
--File activation failure. The physical file name "c:\backup\SyntaxTest_log.ldf" may be incorrect.
--New log file 'C:\SyntaxTest_log.LDF' was created.

Creating Database Snapshot

CREATE DATABASE SyntaxTest_snapshot   
ON (NAME = N'SyntaxTest',     
	FILENAME = N'c:\SyntaxTest_snapshot.snap'  )   
AS SNAPSHOT OF SyntaxTest;