Creating and Deploying Multiple Versions of Database through Schema Snapshots

Overview

This article talks about using database schema snapshots to maintain different versions of a database to be deployed to different environments.

Database schema snapshots are point-in-time copies of the current state of the database which are normally used to reconcile the differences when deploying changes from one environment to another environment.

This article will be focused on a particular scenario where database schema snapshots are more than just point-in-time copies of the database rather they are used to create fresh versions of specific environments.

What is Database Schema Snapshot

A database schema snapshot is simply a saved point in time copy of a database.

In other words, a database schema snapshot is an exact copy of the structure of the database which does not include data in its original form.

Database schema refers to all the database objects including tables, views and stored procedures. We create a database schema snapshot to freeze the objects definitions for later use.

Why is Database Schema Snapshot Needed

Database schema snapshots can be used for the following purposes:

1. Copying an existing state of a database for future reference or future use.
2. Versioning a database through multiple database schema snapshots.
3. Creating a point in time copy of the database structure for quick restore.
4. Creating a copy of the target database schema before deploying new changes.
5. Creating a most recent stable copy of the database schema before proceeding further with more changes.
6. Creating and sharing database changes to an external team member who cannot directly access the database environment.
7. Database schema snapshot can also be used to compare the differences between current work and the work done in the past.
8. Database schema snapshots can be also be used for disconnected publishing.

Requirement to Keep Multiple Database Versions

If your database development team has received a special requirement to keep and maintain multiple database versions to be deployed across multiple environments then one of the solutions is to use database schema snapshots to meet the requirement.

Creating Multiple Database Versions

As discussed earlier, database schema snapshots are not only used as point-in-time copies of the database structure but can also be used to create and deploy multiple database versions at the same time.

Sample Database Setup (TechnicalTraining)

Open dbForge Studio for SQL Server or SSMS (SQL Server Management Studio) to set up a sample database called TechnicalTraining which contains the information about technical courses, students and trainers with the few tables as follows:

-- (1) Creating TechnicalTraining sample database 
CREATE DATABASE TechnicalTraining;
GO

USE TechnicalTraining

-- (2) Creating Student table
CREATE TABLE Student (
  StudentId INT IDENTITY
 ,Name VARCHAR(50) NOT NULL
 ,RegistrationDate DATETIME2 NULL
 ,Notes VARCHAR(200) NULL
 ,CONSTRAINT PK_Student_StudentId PRIMARY KEY CLUSTERED (StudentId)
)
GO

-- (3) Creating Trainer table
CREATE TABLE Trainer (
  TrainerId INT IDENTITY
 ,Name VARCHAR(50) NOT NULL
 ,Qualification VARCHAR(50) NOT NULL
 ,Notes VARCHAR(200) NULL
 ,CONSTRAINT PK_Trainer_TrainerId PRIMARY KEY CLUSTERED (TrainerId)
)
GO

-- (4) Creating Course table
CREATE TABLE Course (
  CourseId INT IDENTITY
 ,Name VARCHAR(50) NOT NULL
 ,TrainerId INT NULL
 ,Detail VARCHAR(200) NULL
 ,CONSTRAINT PK_Course_CourseId PRIMARY KEY CLUSTERED (CourseId)
) ON [PRIMARY]
GO

ALTER TABLE Course
ADD CONSTRAINT FK_Course_TrainerId FOREIGN KEY (TrainerId) REFERENCES dbo.Trainer (TrainerId)
GO


-- (5) Creating StudentCourse table 
CREATE TABLE [dbo].[StudentCourse] (
    [StudentCourseId] INT  IDENTITY(1,1) NOT NULL,
    [StudentId]       INT            NULL,
    [CourseId]        INT            NULL,
    [PercentScore]    DECIMAL (5, 2) NULL,
    CONSTRAINT [PK_StudentCourse_StudentCourseId] PRIMARY KEY CLUSTERED ([StudentCourseId] ASC),
    CONSTRAINT [FK_StudentCourse_Student_StudentId] FOREIGN KEY ([StudentId]) REFERENCES [dbo].[Student] ([StudentId]),
    CONSTRAINT [FK_StudentCourse_Course_CourseId] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])
);
GO

-- (6) Create view to see student progress report
CREATE VIEW StudentProgress as
SELECT s.Name AS StudentName,c.Name as CourseName,t.Name AS Trainer,sc.PercentScore FROM StudentCourse sc 
INNER JOIN Student s
on s.StudentId=sc.StudentId
INNER JOIN Course c
on c.CourseId=sc.CourseId
INNER JOIN Trainer t
on t.TrainerId=c.TrainerId
GO

Please note that the TechnicalTraining database is structured in such a way that many students can take many courses while each course can have only one trainer as shown below:

Please note that I am using dbForge Studio for SQL Server so the output look may differ if you run the same code in SSMS (SQL Server Management Studio). However, there is no difference between scripts and their results.

Populate the database using the following script:

USE TechnicalTraining

-- (1) Populating Trainer table
SET IDENTITY_INSERT [dbo].[Trainer] ON
INSERT INTO [dbo].[Trainer] ([TrainerId], [Name],  [Qualification], [Notes]) VALUES (1, N'George', N'MSc Computer Science', NULL)
INSERT INTO [dbo].[Trainer] ([TrainerId], [Name],  [Qualification], [Notes]) VALUES (2, N'Akeel', N'MSc Database Management', NULL)
INSERT INTO [dbo].[Trainer] ([TrainerId], [Name],  [Qualification], [Notes]) VALUES (3, N'Sarah', N'MSc Data Science', NULL)
INSERT INTO [dbo].[Trainer] ([TrainerId], [Name],  [Qualification], [Notes]) VALUES (4, N'Ben', N'BSc Computer Science', NULL)
SET IDENTITY_INSERT [dbo].[Trainer] OFF


-- (2) Populating Course table
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT INTO [dbo].[Course] ([CourseId], [Name], [TrainerId], [Detail]) VALUES (1, N'Database Development', 1, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [Name], [TrainerId], [Detail]) VALUES (2, N'Data Analysis ', 2, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [Name], [TrainerId], [Detail]) VALUES (3, N'Data Reports Development', 2, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [Name], [TrainerId], [Detail]) VALUES (4, N'Basics of Business Intelligence', 3, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [Name], [TrainerId], [Detail]) VALUES (5, N'Big Data Fundamentals', 4, NULL)
SET IDENTITY_INSERT [dbo].[Course] OFF


-- (3) Populating Student table
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT INTO [dbo].[Student] ([StudentId], [Name],  [RegistrationDate], [Notes]) VALUES (1, N'Asif', N'2017-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Student] ([StudentId], [Name],  [RegistrationDate], [Notes]) VALUES (2, N'Mike', N'2017-02-01 00:00:00', NULL)
INSERT INTO [dbo].[Student] ([StudentId], [Name],  [RegistrationDate], [Notes]) VALUES (3, N'Naveed', N'2017-03-10 00:00:00', NULL)
INSERT INTO [dbo].[Student] ([StudentId], [Name],  [RegistrationDate], [Notes]) VALUES (4, N'Sam', N'2017-04-15 00:00:00', NULL)
INSERT INTO [dbo].[Student] ([StudentId], [Name],  [RegistrationDate], [Notes]) VALUES (5, N'Mona', N'2017-07-10 00:00:00', NULL)
SET IDENTITY_INSERT [dbo].[Student] OFF

-- (4) Populating StudentCourse table
SET IDENTITY_INSERT [dbo].[StudentCourse] ON
INSERT INTO [dbo].[StudentCourse] ([StudentCourseId], [StudentId], [CourseId], [PercentScore]) VALUES (1, 1, 1, CAST(72.00 AS Decimal(5, 2)))
INSERT INTO [dbo].[StudentCourse] ([StudentCourseId], [StudentId], [CourseId], [PercentScore]) VALUES (2, 1, 2, CAST(75.00 AS Decimal(5, 2)))
INSERT INTO [dbo].[StudentCourse] ([StudentCourseId], [StudentId], [CourseId], [PercentScore]) VALUES (3, 2, 2, CAST(80.00 AS Decimal(5, 2)))
INSERT INTO [dbo].[StudentCourse] ([StudentCourseId], [StudentId], [CourseId], [PercentScore]) VALUES (4, 2, 3, CAST(70.00 AS Decimal(5, 2)))
INSERT INTO [dbo].[StudentCourse] ([StudentCourseId], [StudentId], [CourseId], [PercentScore]) VALUES (5, 3, 5, CAST(80.00 AS Decimal(5, 2)))
SET IDENTITY_INSERT [dbo].[StudentCourse] OFF

Database Check

Right-click StudentProgress under the Views folder and click Retrieve Data or alternatively type the following T-SQL code:

-- View students progress
SELECT s.Name,c.Name as CourseName,t.Name,sc.PercentScore FROM StudentCourse sc
INNER JOIN Student s
on s.StudentId=sc.StudentId
INNER JOIN Course c
on c.CourseId=sc.CourseId
INNER JOIN Trainer t
on t.TrainerId=c.TrainerId
order by s.Name

The output is as follows:

Setup Version 1 by Creating Database Schema Snapshot

This is the time to save the point-in-time copy of the database schema since the current database structure fulfills the requirements for version 1 of the database.

Create Schema Snapshot Version 1

Right-click the TechnicalTraining database in Database Explorer of dbForge Studio for SQL Server (or you can use any similar tool of your choice which is capable of creating database schema snapshot), click Tasks and then click Create Scripts Folder or Snapshot… as shown below:

Create a snapshot at your desired location and name it TechnicalTraining-Version-001-StudentCourseTrainer.snap as follows:

Check Schema Snapshot Version 1

Check the folder to view the recently created database schema snapshot of version 1:

Add New Table CourseType

Let us now add another table called CourseType to the existing database with help of the following script:

-- Adding CourseType table 
CREATE TABLE CourseType (
  CourseTypeId INT IDENTITY
 ,Name VARCHAR(50) NOT NULL
 ,Detail VARCHAR(250) NULL
 ,CONSTRAINT PK_CourseType_CourseId PRIMARY KEY CLUSTERED (CourseTypeId)
);
GO

Insert data into the table as follows:

SET IDENTITY_INSERT [dbo].[CourseType] ON
INSERT INTO [dbo].[CourseType] ([CourseTypeId], [Name], [Detail]) VALUES (1, N'Basic', NULL)
INSERT INTO [dbo].[CourseType] ([CourseTypeId], [Name], [Detail]) VALUES (2, N'Intermediate', NULL)
INSERT INTO [dbo].[CourseType] ([CourseTypeId], [Name], [Detail]) VALUES (3, N'Advanced', NULL)
SET IDENTITY_INSERT [dbo].[CourseType] OFF

Modify Course Table to Add CourseType Column

Update the Course table to add the CourseType foreign key:

-- Drop foreign key constraint
ALTER TABLE StudentCourse
Drop Constraint [FK_StudentCourse_Course_CourseId]

-- Drop Course table
DROP TABLE Course

-- Create Course table with new column CourseTypeId
CREATE TABLE [dbo].[Course] (
    [CourseId]  INT           IDENTITY (1, 1) NOT NULL,
    [CourseTypeId] INT,
	[Name]      VARCHAR (50)  NOT NULL,
    [TrainerId] INT           NULL,
    [Detail]    VARCHAR (200) NULL,
    CONSTRAINT [PK_Course_CourseId] PRIMARY KEY CLUSTERED ([CourseId] ASC),
    CONSTRAINT [FK_Course_TrainerId] FOREIGN KEY ([TrainerId]) REFERENCES [dbo].[Trainer] ([TrainerId]), 
    CONSTRAINT [FK_Course_CourseTypeId] FOREIGN KEY ([CourseTypeId]) REFERENCES [CourseType]([CourseTypeId])
);
GO

Add data to the newly modified Course table as follows:

-- Add data to the Course table
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT INTO [dbo].[Course] ([CourseId], [CourseTypeId], [Name], [TrainerId], [Detail]) VALUES (1, 1, N'Database Development', 1, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [CourseTypeId], [Name], [TrainerId], [Detail]) VALUES (2, 3, N'Data Analysis ', 2, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [CourseTypeId], [Name], [TrainerId], [Detail]) VALUES (3, 2, N'Data Reports Development', 2, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [CourseTypeId], [Name], [TrainerId], [Detail]) VALUES (4, 1, N'Basics of Business Intelligence', 3, NULL)
INSERT INTO [dbo].[Course] ([CourseId], [CourseTypeId], [Name], [TrainerId], [Detail]) VALUES (5, 1, N'Big Data Fundamentals', 4, NULL)
SET IDENTITY_INSERT [dbo].[Course] OFF

-- Add Foreign key constraint back to StudentCourse table
ALTER TABLE StudentCourse ADD CONSTRAINT [FK_StudentCourse_Course_CourseId] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])

Add New View CoursesWithTypes

Now add a new view to see all the courses with their types as follows:

-- Creating a view to see courses with their types
Create VIEW CoursesWithTypes AS
SELECT c.CourseId,c.Name as CousreName,ct.Name as CourseType FROM dbo.Course c inner join dbo.CourseType ct
on c.CourseTypeId=ct.CourseTypeId;
GO

Database Check

View the database structure to see the most recent changes:

Run the view CoursesWithTypes:

Setup Version 2 by Creating Database Schema Snapshot

Create another point-in-time copy of the database structure to mark Version 2 of the database.

Create a Database Schema Snapshot and call it TechnicalTraining-Version-002-StudentCourseTrainerCourseType.snap as follows:

Deploying Multiple Database Versions

After successful creation of database schema snapshots for version 1 and version 2, we can now deploy any version to any environment as per requirement.

Creating Dev Database from Schema Snapshot Version 1

Click Comparison-> New Schema Comparison from the menu bar in dbForge Studio for SQL Server:

Next set Source Type as Snapshot and Locate the Database Schema Snapshot version 1 TechnicalTraining-Version-001-StudentCourseTrainer.snap we created earlier and click the plus symbol to create target database on the fly:

Type in the database name TechnicalTrainingV1_DEV and click OK:

Click Next:

Click Next again to select default Options and then click Next to continue with default Schema Mapping options and then click Compare:

Synchronize Source and Target by clicking Green Icon in the middle as shown below:

After a few more steps, click Synchronize:

Next, execute the script to finally create the TechnicalTrainingV1_DEV database from the database schema snapshot representing version 1 of the database:

View the newly created database which is a copy of TechnicalTraining database version 1:

Creating Dev Database from Schema Snapshot Version 2

Now create Version 2 of the database by following the steps mentioned to create Version 1 simply by pointing to Version 2 Database Schema Snapshot TechnicalTraining-Version-002-StudentCourseTrainerCourseType this time:

Comparing Version 1 and Version 2

Let us quickly compare both databases to see the differences.

Congratulations! You have successfully created multiple versions of the database through database schema snapshots.

Things to Do

You can now easily make use of database schema snapshots to create and deploy multiple versions of the database.

1. Create multiple versions of SQLDevBlogTDD mentioned in my previous article.
2. Create multiple versions of the database with version 1 meeting Total Number of Articles per Author Report requirement and version 2 meeting Total Number of Articles per Year Report.
3. Go through my previous article Art of Isolating Dependencies and Data in Database Unit Testing and see if you can create two different versions of the database through database schema snapshots one with tSQLt unit tests and the other without tSQLt unit tests.

Useful tool:

dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.