Contained Database example in SQL Server

SQL Server 2012 introduced a new feature named "Contained Database" which include all database settings and metadata along with database backup. It allows us to move database backups along with its users to another server, so no need any dependencies on the server. Let us look an example.

1. Enable a setting

 
-- Enable setting for contained database
Use master
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE
GO 
exec sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE
GO 

2. Enable a feature for a database

 
-- Create a new contained database

CREATE DATABASE [ContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY
 ( NAME = N'ContainedDB_Data', FILENAME = N'D:\Data\ContainedDB_Data.mdf')
LOG ON
 ( NAME = N'ContainedDB_Log', FILENAME = N'D:\Log\ContainedDB_Log.ldf')
GO

-- Or change existing database to contained
ALTER DATABASE [ContainedDB] SET CONTAINMENT = PARTIAL
GO

3. Create a new SQL User

 
-- Create a new SQL User in this contained database
USE [ContainedDB]
GO
CREATE USER ContainedDBUser WITH PASSWORD = 'ContainedDBUser'
GO

Checking : Let us login with this User created and below error occur .

 Now change a setting when login with this User.

1. Go to 'Options<<'
2. Move on tab 'Connection  Properties'
3. Set 'Connect to database' to "ContainedDB"

After applying an option, you will be succeed. So wherever you want to move contained database to another server, just move it and log in as options stated above. Enjoy Contained Database!