Creating and Deploying Multiple Versions of Database through Schema Snapshots

Creating and Deploying Multiple Versions of Database through Schema Snapshots
5 (100%) 1 vote

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:

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:

Database Check

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

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:

Insert data into the table as follows:

Modify Course Table to Add CourseType Column

Update the Course table to add the CourseType foreign key:

Add data to the newly modified Course table as follows:

Add New View CoursesWithTypes

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

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 following my previous article.
  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.
Haroon Ashraf

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf