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:
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:
Click the install button to proceed further:
You will be asked to open the GitHub repository:
This will take you to the GitHub repository (page) of this extension from where it can be downloaded:
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:
Please point to the downloaded extension to install it:
Once installed you will see the following screen:
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:
Setup a database called SQLBooksSample by running the following script against the master database:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Create a new database called 'SQLBooksSample' -- Connect to the 'master' database to run this snippet USE master GO -- Create the new database if it does not exist already IF NOT EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = N'SQLBooksSample' ) CREATE DATABASE SQLBooksSample GO |
Create a table in the database SQLBooksSample as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Create a new table called '[Book]' in schema '[dbo]' -- Drop the table if it already exists IF OBJECT_ID('[dbo].[Book]', 'U') IS NOT NULL DROP TABLE [dbo].[Book] GO -- Create the table in the specified schema CREATE TABLE [dbo].[Book] ( [BookId] INT NOT NULL PRIMARY KEY, -- Primary Key column [Title] NVARCHAR(50) NOT NULL, -- Specify more columns here ); GO |
Now right-click on the Databases and click Refresh followed by expanding Databases node to view the newly created SQL database with Book table:
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:
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:
Now, click Run to create a database snapshot:
The first database snapshot is 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:
|
1 2 3 4 5 6 7 8 9 10 |
-- Insert rows into table 'Book' in schema '[dbo]' INSERT INTO [dbo].[Book] ( -- Columns to insert data into [BookId], [Title] ) VALUES ( -- First row: values for the columns in the list above 1,'Learn SQL from A to Z' ) GO |
View the inserted row:
|
1 2 3 |
SELECT [BookId] ,[Title] FROM [SQLBooksSample].[dbo].[Book] |
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:
Using the same method run the script (which was automatically generated) to create a second snapshot:
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:
You will be asked to confirm your changes:
Click on the query window title bar to be able to run the script:
Next view the contents of the Book table after the database was restored from the snapshot:
|
1 2 |
-- View table Book after restoring the database from the first snapshot that contains 0 row(s) SELECT B.BookId,B.Title FROM dbo.Book B |
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:
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:
|
1 2 |
-- View table Book after restoring the database from the second snapshot that contains one row SELECT B.BookId,B.Title FROM dbo.Book B |
The output is as follows:
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 |
| Migrating on-premises databases to Azure SQL Database in Azure Data Studio |
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
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023