Haroon Ashraf
Searching DB Snapshot Creator extension in the Extensions section of Azure Data Studio

Using the DB Snapshot Creator Extension in Azure Data Studio

June 8, 2021 by

This article talks about the steps required to add and use the DB Snapshot Creator extension in Azure Data Studio.

Additionally, the readers are going to get a conceptual understanding of database snapshots and their use in professional life scenarios. This article highlights the importance of preserving database structure for future reference.

Let us get familiar with the extension prior to its use.

What is the DB Snapshot Creator Extension?

This extension assists in creating multiple database snapshots in a particular order distinguished by date stamps and serial numbers.

What is a database snapshot?

A database snapshot is a copy of an existing database at a certain point in time. In other words, a database snapshot helps database developers and DBAs to create a point-in-time version of an existing database. A database snapshot preserves both structure and data.

Can database snapshot be edited?

A database snapshot is a read-only copy of an existing database so it cannot be edited or modified. For example, if you try to insert a new row into a table of the snapshot it will not be inserted.

Does Snapshot need to be created on the same Server?

Yes, a database snapshot needs to be created on the same server where the original database exists.

Can a database snapshot be created with the help of a T-SQL script?

Yes, you can create a database snapshot with the help of a T-SQL script but using the DB Snapshot Creator extension makes your job easier as it generates the script for you and you only have to run it.

Where can I learn more about Database Snapshot?

You can get more information about database snapshot by referring to the following articles:

  1. SQL Server Database Snapshots
  2. Database snapshot in SQL Server

Installing the DB Snapshot Creator Extension in Azure Data Studio

One of the best things about Azure Data Studio is that you can instantly equip it with your required tools by simply adding the extensions according to the business requirements or your development taste.

Azure Data Studio does not fall short of becoming the first-hand choice of Data professionals including database or data warehouse developers who prefer coding (scripting) over using graphical user interfaces to achieve an objective be it developing a SQL database project or creating a series of unit tests.

However, on the flip side, there are DB Snapshot Creator-like extensions that generate code for the developers to do specific database-related tasks with the flexibility to modify the code as per requirements.

Let us start with the installation of the DB Snapshot Creator extension but do bear in mind that this extension at the time of writing this article is installed in a slightly different manner as compared to the extensions that simply get added by clicking the install button.

Let’s head over to Azure Data Studio and switch to the Extensions followed by typing DB Snapshot in the search box as shown below:

Searching DB Snapshot Creator extension in the Extensions section of Azure Data Studio

Click the install button to proceed further:

Clicking Install to proceed further with the installation.

You will be asked to open the GitHub repository:

Open Github repository of the extension.

This will take you to the GitHub repository (page) of this extension from where it can be downloaded:

DB Snapshot Creator Extension downloaded from the Github repository to be installed in Azure Data Studio

Once it is downloaded you need to get back to Azure Data Studio and open the File menu and click Install Extension from VSIX Package:

Installing extension from VSIX Package in Azure Data Studio

Please point to the downloaded extension to install it:

Pointing to the downloaded extension for installation

Once installed you will see the following screen:

Database Snapshot Creator extension installed successfully

Using DB Snapshot Creator Extension

Let us see a demo of using this extension against a sample database to understand how it works. Switch to the Servers section in Azure Data Studio and connect to your local/remote SQL Server instance:

Connected to local/remote server in Azure Data Studio Connections Section

Setup a database called SQLBooksSample by running the following script against the master database:

Create a table in the database SQLBooksSample as follows:

Now right-click on the Databases and click Refresh followed by expanding Databases node to view the newly created SQL database with Book table:

Sample database SQLBooksSample

Creating first database snapshot

Now we can create a snapshot of this sample database with one blank table (no rows) in Azure Data Studio.

Right-click on SQLBooksSample database and click on Create Snapshot of this database:

Creating snapshot of the database that has one blank table called Book

You will see a snapshot script generated in the query window. This does seem a bit odd but clicks on the window title bar to be able to run this script:

Snapshot creation script ready to run

Now, click Run to create a database snapshot:

Running the script to create database snapshot

The first database snapshot is created successfully:

Database snapshot created successfully

Now go back to the original SQLBooksSample database and run the following script against it to insert one row into the Book table:

View the inserted row:

Viewing table Book that has one new record (row)

Creating a second database snapshot

Once again right-click on the database SQLBooksSample and click Create snapshot of this database to create another snapshot of the database:

Creating second snapshot of the database

Using the same method run the script (which was automatically generated) to create a second snapshot:

Database with two snapshots.

We have successfully created two snapshots of the database where the first snapshot contains a table without any data (rows) and the second snapshot contains a table with data (one row).

Revert Database from the first snapshot

As we know that the database SQLBookSample has a table with one record in it and this is going to be changed if we revert the database from the first snapshot because the first snapshot contains a blank table.

So, we are expecting the current Book table in the sample database with one row to be replaced with the snapshot version with no rows at all.

Now right-click on the first snapshot SQLBooksSample_Snapshot_04_07_2021_02 and click Revert Database from snapshot:

Clicking Revert database from snapshot

You will be asked to confirm your changes:

Confirming the changes to be brought in by reverting the database from the snapshot

Click on the query window title bar to be able to run the script:

Opening connection tool bar by clicking on the query window title bar

Opening connection tool bar

Next view the contents of the Book table after the database was restored from the snapshot:

Book table has no rows now

Clearly, we can see the data is gone since the first snapshot had no rows in the Book table.

Revert Database from the second snapshot

Using the similar method as shown above, this time revert the database from the second snapshot:

Reverting the database from the second snapshot

Let us see the results once we have successfully reverted the database SQLBooksSample from the second snapshot SQLBooksSample_Snapshot_04-07-2021_02.

You can view the contents of the table Book by running the same script against the sample database:

The output is as follows:

Viewing the sample database Book table after reverting the database from the second snapshot

A word of advice

There can be many professional life scenarios where a database snapshot is handy, however, do bear in mind that a database snapshot is just a read-only copy of the database unlike database backup, and must not be used for that (backup) purpose.

One of the many practical examples of using database snapshots is for testing a database to add and remove test data by using database snapshots to ensure that the database remains in its original form free from any test data when getting prepared to be handed over to the target environment.

Another use case of a database snapshot is when you are about to run a critical data update and would like to ensure that you have a rollback strategy other than the backup restore option.

It is not highly recommended to have a lot of database snapshots for no reason since you may end up missing the point of having these snapshots and one good strategy is to keep on refreshing the snapshots by creating regular daily snapshots replacing the existing ones except if this does not comply with your business or technical requirements and there is no harm in keeping the notes of the snapshots safe for future reference.

Finally, in my personal opinion, the DB Snapshot Creator extension is a good addition to the existing extensions for databases, however, it may require some improvement to give a more intuitive and robust experience especially (in one test case) when used with the default SQL instance on two occasions it failed to recognize my database snapshot created through it but afterward, it was right on spot.

Summary

To sum up, we learned to install and use the DB Snapshot Creator extension in Azure Data Studio by creating multiple snapshots of a sample database followed by reverting the sample database to these snapshots one by one in order to understand the benefit of using these point-in-time read-only copies (database snapshots) of a database.

Table of contents

Two ways to build SQL Database Projects in Azure Data Studio
How to use SQL Server DACPAC extensions in Azure Data Studio
Declarative Database Development in Azure Data Studio
Using the DB Snapshot Creator Extension in Azure Data Studio

Haroon Ashraf
Azure, Azure Data Studio, Development

About 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 holds BSc and MSc Degrees in Computer Science and also received the OPF merit award. He began his professional life as a computer programmer more than a decade 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). He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential. View all posts by Haroon Ashraf

70 Views