01 CREATE DATABASE simple version.sql

USE [master];
GO

-- What version of SQL Server am I running on?
SELECT  SERVERPROPERTY (N'ProductVersion') AS [ProductVersion] ,
        SERVERPROPERTY (N'ProductLevel') AS [ProductLevel];
GO

-- What databases are already on this SQL Server instance?
SELECT  [name] ,
        [database_id] ,
        [create_date] 
FROM sys.[databases];
GO

-- In the simplest form, we can create a database as follows:
CREATE DATABASE [PluralsightDemo];
GO

-- Rules regarding the name:
--   Maximum 128 characters (123 if the logical name isn't
--   specified for the log file)
--   Can't already be used by another database on the same
--   SQL Server instance

-- Viewing databases on the SQL Server instance, full view of
-- things
SELECT  [name] ,
        [database_id] ,
        [source_database_id] ,
        [owner_sid] ,
        [create_date] ,
        [compatibility_level] ,
        [collation_name] ,
        [user_access] ,
        [user_access_desc] ,
        [is_read_only] ,
        [is_auto_close_on] ,
        [is_auto_shrink_on] ,
        [state] ,
        [state_desc] ,
        [is_in_standby] ,
        [is_cleanly_shutdown] ,
        [is_supplemental_logging_enabled] ,
        [snapshot_isolation_state] ,
        [snapshot_isolation_state_desc] ,
        [is_read_committed_snapshot_on] ,
        [recovery_model] ,
        [recovery_model_desc] ,
        [page_verify_option] ,
        [page_verify_option_desc] ,
        [is_auto_create_stats_on] ,
        [is_auto_update_stats_on] ,
        [is_auto_update_stats_async_on] ,
        [is_ansi_null_default_on] ,
        [is_ansi_nulls_on] ,
        [is_ansi_padding_on] ,
        [is_ansi_warnings_on] ,
        [is_arithabort_on] ,
        [is_concat_null_yields_null_on] ,
        [is_numeric_roundabort_on] ,
        [is_quoted_identifier_on] ,
        [is_recursive_triggers_on] ,
        [is_cursor_close_on_commit_on] ,
        [is_local_cursor_default] ,
        [is_fulltext_enabled] ,
        [is_trustworthy_on] ,
        [is_db_chaining_on] ,
        [is_parameterization_forced] ,
        [is_master_key_encrypted_by_server] ,
        [is_published] ,
        [is_subscribed] ,
        [is_merge_published] ,
        [is_distributor] ,
        [is_sync_with_backup] ,
        [service_broker_guid] ,
        [is_broker_enabled] ,
        [log_reuse_wait] ,
        [log_reuse_wait_desc] ,
        [is_date_correlation_on] ,
        [is_cdc_enabled] ,
        [is_encrypted] ,
        [is_honor_broker_priority_on] ,
        [replica_id] ,
        [group_database_id] ,
        [default_language_lcid] ,
        [default_language_name] ,
        [default_fulltext_language_lcid] ,
        [default_fulltext_language_name] ,
        [is_nested_triggers_on] ,
        [is_transform_noise_words_on] ,
        [two_digit_year_cutoff] ,
        [containment] ,
        [containment_desc] ,
        [target_recovery_time_in_seconds]
FROM    sys.[databases];
GO

-- Given no explicit configurations, what are the details
-- of our new database?
EXEC sys.[sp_helpdb] N'PluralsightDemo';
GO

-- Where is the default path designated?
SELECT  SERVERPROPERTY (N'instancedefaultdatapath') AS [DefaultFile] ,
        SERVERPROPERTY (N'instancedefaultlogpath') AS [DefaultLog];

-- The default path is designated in the registry and you
-- can change via SSMS or via xp_instance_regwrite
USE [master];

EXEC [xp_instance_regwrite] N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
	REG_SZ,
    N'S:\SQLskills\MDF';

EXEC [xp_instance_regwrite] N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
	REG_SZ,
    N'S:\SQLskills\LOG';
GO

-- Given no explicit configurations, what are the details
-- of our new database?
EXEC sys.[sp_helpdb] N'PluralsightDemo';
GO

-- Default sizes use the model database
EXEC sys.[sp_helpdb] N'model';
GO

-- Cleanup before the next demo
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

02 Defining files and file groups with your database creation.sql

USE [master];

-- Minimum file requirements = 1 primary file and
-- 1 transaction log file
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY
		-- First file in primary filegroup is the primary file
(	NAME = N'PluralsightDemo',-- logical_file_name 		
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
		-- 'os_file_name' 
	SIZE = 1024MB ,
		-- size [ KB | MB | GB | TB ] ] 
	MAXSIZE = UNLIMITED,
		-- max_size [ KB | MB | GB | TB ] | UNLIMITED 
	FILEGROWTH = 1024MB )
		-- growth_increment [ KB | MB | GB | TB | % ] 
LOG ON -- specifies log file details
(	NAME = N'PluralsightDemo_log',
		-- logical_file_name
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
		-- 'os_file_name' 
	SIZE = 1024MB ,
		-- size [ KB | MB | GB | TB ] ]
	MAXSIZE = 2048GB ,
		-- max_size [ KB | MB | GB | TB ] | UNLIMITED 
	FILEGROWTH = 1024MB);
 -- Avoid growth by percentage!
 -- See Paul Randal's blog post, "Importance of data
 -- file size management", http://bit.ly/18NWw9p
GO

EXEC sp_helpdb 'PluralsightDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

03 Defining a database with multiple data files.sql

USE [master];

-- Multiple data files
-- A maximum of 32,767 files and 32,767 filegroups
-- (not a goal, mind you)
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 
(	NAME = N'PluralsightDemo', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ), 
(	NAME = N'PluralsightDemo_2', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo_2.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB);
GO

-- Why not have multiple transaction log files?

EXEC sp_helpdb 'PluralsightDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

04 Creating a database with a user-defined filegroup.sql

USE [master];

-- User-defined filegroup - why use them?
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 
(	NAME = N'PluralsightDemo', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ), 
FILEGROUP [Application] 
(	NAME = N'PluralsightDemo_2', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo_2.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB);
GO

-- What happens if someone adds a table?
USE [PluralsightDemo];
GO

-- Either reference the filegroup explicitly
CREATE TABLE [dbo].[category]
    (
      [category_no] INT IDENTITY (1, 1)
                        NOT NULL ,
      [category_desc] VARCHAR (31) NOT NULL ,
      [category_code] CHAR (2) NOT NULL ,
      CONSTRAINT [category_ident] PRIMARY KEY CLUSTERED
		( [category_no] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [Application]
    )
ON  [Application];
GO

-- Or alternatively, set the filegroup as the default
-- (I recommend this instead of depending on people to remember)
SELECT  [name]
FROM    sys.[filegroups]
WHERE   [is_default] = 1
        AND name = N'Application';
GO

ALTER DATABASE [PluralsightDemo] 
MODIFY FILEGROUP [Application] DEFAULT;
GO

SELECT  [name]
FROM    sys.[filegroups]
WHERE   [is_default] = 1
        AND name = N'Application';
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

05 Defining collation.sql

USE [master];
GO

/*
	Collations define the code page used for 
	non-Unicode character data type storage and
	sort order for Unicode and non-Unicode character types
*/

-- What is the current SQL Server instance collation?
SELECT  SERVERPROPERTY (N'Collation');
GO

-- What are the current database collation settings?
SELECT  [name] ,
        [database_id] ,
        [collation_name]
FROM    sys.[databases];
GO

-- And what are the collation options?
SELECT  [name] ,
        [description]
FROM    sys.[fn_helpcollations]();
GO

-- Creating a database designating a non-default collation
USE [master];
GO

-- Creating a database with collation explicitly set
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB) 
COLLATE French_CS_AS;
GO

-- We can validate in sys.databases, or also use the
-- following technique
SELECT  DATABASEPROPERTYEX (N'PluralsightDemo', N'Collation')
	AS [Collation];

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

06 Recovery models.sql

USE [master];
GO

/*
	Recovery model choices:
		
		Simple:
			Recovery to the end of the last full backup, no log 
			backups

		Bulk logged:
			All backup types permitted, reduced log space for 
			minimal logged, bulk operations, but additional risk
			exposure if log is damaged and a limit on
			point-in-time recovery
		
		Full:
			All backup types permitted,point-in-time recovery 
			possible

		See Paul Randal's course "SQL Server: Logging, Recovery,
			and the Transaction Log"
							http://bit.ly/SiSwVS
*/

-- What are the current database settings?
SELECT  [name] ,
        [database_id] ,
        [name] ,
        [recovery_model] ,
        [recovery_model_desc]
FROM    sys.[databases];
GO

-- Create a database
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB);
GO

-- We can validate in sys.databases, or also use the
-- following technique
SELECT  DATABASEPROPERTYEX (N'PluralsightDemo', N'Recovery')
	AS [Recovery Model];
GO

-- Modifying the "model" system database
ALTER DATABASE [model] 
SET RECOVERY SIMPLE; 
GO

CREATE DATABASE [PluralsightDemo2] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo2',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo2.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo2_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo2_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB);
GO

-- We can validate in sys.databases, or also use the
-- following technique
SELECT  DATABASEPROPERTYEX(N'PluralsightDemo2', N'Recovery')
	AS [Recovery Model];
GO

-- Modifying the [model] database recovery model back to FULL
ALTER DATABASE [model] SET RECOVERY FULL;
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

DROP DATABASE [PluralsightDemo2];
GO

07 Database ownership.sql

USE [master];
GO

-- Who are the various database owners?
SELECT  [d].[name] ,
        [d].[database_id] ,
        [d].[source_database_id] ,
        [d].[owner_sid] ,
        [sp].[name] ,
        [d].[create_date]
FROM    sys.[databases] AS [d]
LEFT OUTER JOIN sys.[server_principals] AS [sp] ON 
	[d].[owner_sid] = [sp].[sid];

-- Alternate method
EXEC sp_helpdb;
GO

-- Why does it matter?  One example would be for
--  "EXECUTE as owner" modules 

-- We'll cover how to change ownership in another module

08 DB_CHAINING and TRUSTWORTHY.sql

USE [master];
GO

-- DB_CHAINING
-- Be VERY cautious about enabling this option!
-- Allows the database to be a target or source for
-- cross-database ownership chains.
-- If the sp_configure 'cross db ownership chaining' is enabled,
-- all databases can cross-database chain anyhow.
-- You can't configure this for master, model and tempdb

-- TRUSTWORTHY
-- Again be VERY cautious about enabling this option!
-- Impersonation context via modules can exit the boundaries of
-- the database.
-- Off by default except the msdb database and can't be changed
-- for model and tempdb.

CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB) 
WITH DB_CHAINING OFF, TRUSTWORTHY OFF; -- showing you the explicit OFF
GO

-- Confirming the settings
SELECT  [name] ,
        [database_id] ,
        [source_database_id] ,
        [owner_sid] ,
        [is_trustworthy_on] ,
        [is_db_chaining_on]
FROM    sys.[databases];
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

09 Creating a database by attaching a file.sql

USE [master];
GO

CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB);
GO

-- Detaching a database
USE [master];
GO

-- @dbname =  'database_name' (self-explanatory)
-- @skipchecks =  'skipchecks' 
	-- If "false" run UPDATE STATISTICS prior to detach (default)
EXEC master.dbo.sp_detach_db @dbname = N'PluralsightDemo',
    @skipchecks = 'false';
GO

-- Attaching a database
USE [master];
GO

CREATE DATABASE [PluralsightDemo] ON 
( FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ),
( FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' )
    FOR ATTACH;
GO

-- Detach again
EXEC master.dbo.sp_detach_db @dbname = N'PluralsightDemo',
    @skipchecks = 'false';
GO

-- Attaching without the transaction log (implicit attach)
USE [master];

CREATE DATABASE [PluralsightDemo] ON 
( FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' )
    FOR ATTACH;
GO

-- Detach again
EXEC master.dbo.sp_detach_db @dbname = N'PluralsightDemo',
    @skipchecks = 'false';
GO

-- Let's say we lost the transaction log (let's delete it)

-- Attaching without the transaction log (explicit rebuild)
-- Requires a clean shutdown of the database and availability
-- of all data files
CREATE DATABASE [PluralsightDemo] ON 
( FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' )
    FOR ATTACH_REBUILD_LOG;
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

10 Creating database snapshots.sql

USE [master];
GO

CREATE DATABASE [PluralsightDemo] ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB);
GO

-- Let's create a table and add rows
USE [PluralsightDemo];
GO

CREATE TABLE [dbo].[category]
    (
      [category_no] INT IDENTITY (1, 1)
                        NOT NULL ,
      [category_desc] VARCHAR (31) NOT NULL ,
      [category_code] CHAR (2) NOT NULL ,
      CONSTRAINT [category_ident]
			PRIMARY KEY CLUSTERED ( [category_no] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY];
GO

INSERT  [dbo].[category]
        ( [category_desc] ,
          [category_code]
        )
VALUES  ( N'Confections' ,
          N'CF'
        ),
		( N'Beverages' ,
          N'BV'
        ),
		( N'Poultry' ,
          N'PL'
        );
GO

-- Need data as-of a specific period of time?
-- Need to revert data "as of" a specific period of time?
-- (not a substitute for a backup \ recovery strategy)
CREATE DATABASE [PluralsightDemo_Snapshot] ON (
	    NAME = N'PluralsightDemo', -- logical file name
	    FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdfss')
    AS SNAPSHOT OF [PluralsightDemo];
GO 

-- Let's insert a few more rows
INSERT  [dbo].[category]
        ( [category_desc] ,
          [category_code]
        )
VALUES  ( N'Produce' ,
          N'PR'
        ),
		( N'Cereals' ,
          N'CE'
        ),
	   ( N'Meat' ,
          N'MT'
        );
GO

-- Rows in the PluralsightDemo database table
SELECT  [category_desc] ,
        [category_code]
FROM    [dbo].[category];
GO

-- Rows from the snapshot
SELECT  [category_desc] ,
        [category_code]
FROM    [PluralsightDemo_Snapshot].[dbo].[category];
GO

-- Reverting from snapshot (again, not a substitute for a
-- full backup/recovery plan!)
-- Also - beware the following bug:
-- Bug: reverting from a database snapshot shrinks the
-- transaction log to 0.5MB (http://bit.ly/RJxfWf)
USE [master];
GO

RESTORE DATABASE [PluralsightDemo] 
FROM DATABASE_SNAPSHOT = 'PluralsightDemo_Snapshot';
GO 

-- Rows in the PluralsightDemo database table
USE [PluralsightDemo];
GO

SELECT  [category_desc] ,
        [category_code]
FROM    [dbo].[category];
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo_Snapshot];
GO

DROP DATABASE [PluralsightDemo];
GO

11 Accommodating FILESTREAM data.sql

USE [master];
GO

-- Assumption is that the FILESTREAM feature is enabled
-- See the BOL topic, Enable and Configure FILESTREAM
-- http://bit.ly/fuVMeR
-- And note the step "Restart the SQL Server service"  

-- SQL Server Configuration Manager 

-- SQL Server instance option changes
EXEC sp_configure filestream_access_level, 2;
GO
 -- Enables FILESTREAM Transact-SQL and Win32 streaming access
RECONFIGURE;
GO

CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ), 
FILEGROUP [FileStreamFileGroup] 
CONTAINS FILESTREAM
   (NAME = FileStreamDocuments,
      FILENAME = N'S:\SQLskills\MDF\Documents') 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB);
GO

-- A simple example
USE [PluralsightDemo];
GO

CREATE TABLE [dbo].[FSTest] (
   [FSTestID] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1),
   [FSTestGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [DocumentNM] VARCHAR (50) NOT NULL,
   [DocumentFS] VARBINARY (MAX) FILESTREAM)
FILESTREAM_ON [FileStreamFileGroup];
GO

INSERT [dbo].[FSTest]
([FSTestGUID],[DocumentNM], [DocumentFS])
SELECT NEWID (),
	   'My test file',
	   [BulkColumn]
FROM OPENROWSET(BULK 'S:\SQLskills\testfile.txt', SINGLE_BLOB) AS [o];

SELECT 	[FSTestGUID],
		[DocumentNM], 
		[DocumentFS],
		[DocumentFS].PathName () AS [PathName]
FROM dbo.[FSTest];
GO

-- And there is much more to consider around FILESTREAM, so be sure to check out:
-- Paul Randal's FILESTREAM blog posts, http://bit.ly/IkPYVD

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

12 Containment settings.sql

USE [master];
GO

-- First we need to enable it at the SQL Server instance scope
EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

-- Next, we can create the contained database
CREATE DATABASE [PluralsightDemo] 
 CONTAINMENT = PARTIAL -- options are NONE or PARTIAL
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB)
WITH DEFAULT_FULLTEXT_LANGUAGE = 1033, -- Default full-text language option is used in a full-text index when no language is specified 
	 DEFAULT_LANGUAGE = 1033;  -- Default language for the new user
GO

-- To demonstrate, let's create a test SQL Login
USE [master];
GO

CREATE LOGIN [TestLogin] 
WITH PASSWORD = N'PUwHT8bsDr4bxk', 
	 DEFAULT_DATABASE = [PluralsightDemo], 
	 CHECK_EXPIRATION = OFF, 
	 CHECK_POLICY = OFF;
GO

USE [PluralsightDemo];
GO

CREATE USER [TestLogin] 
	FOR LOGIN [TestLogin];
GO

-- But for existing SQL Login / SQL User mappings, we can
-- make them contained
EXECUTE sp_migrate_user_to_contained 
    @username = N'TestLogin',
    @rename = N'keep_name',
    @disablelogin = N'disable_login';
GO

-- See Object Explorer for the results

-- And we can create the contained user natively as well
USE [PluralsightDemo];
GO

CREATE USER [TestLogin2] 
WITH PASSWORD = N'SQPuO5QzrYqy5T';
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

DROP LOGIN [TestLogin];
GO