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
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!
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 GO2. 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 GO3. Create a new SQL User
-- Create a new SQL User in this contained database USE [ContainedDB] GO CREATE USER ContainedDBUser WITH PASSWORD = 'ContainedDBUser' GOChecking : 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!
Comments
Post a Comment