Like many Azure Data Studio extensions, DB Snapshot Creator is designed to bring functionality into ADS that is not present by default. This extension was developed by Sean Price. As the name suggests, this extension can be used to easily create database snapshots. Before going too deep into this extension, let’s take a quick moment to go over what a snapshot is.
First of all don’t think of it as a backup. Think of it as a read-only copy of the database. As pages are modified for the first time in the source database, SQL Server will move the original unmodified pages to the snapshot. This process will allow users to see that snapshot that is a mirror of the database when the snapshot was created. You might be asking, why should I use a snapshot? There are a number of reasons, but the biggest in my mind is the ability to use the snapshot for reporting purposes.
What if an 8-K page is modified twice? SQL Server will only copy the page to the snapshot the first time it is modified. After that, the page will not be copied again.
In Microsoft’s documentation, found here, right at the beginning of the document MS makes one very important point.
“Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.”
To find if there are any snapshots you can use the code below. This code will not only tell you if snapshots exist, it will also return what database it is associated with and when it was created.
SELECT a.name AS ‘Snapshot Name’
, a.create_date AS ‘Snapshot Create Date’
, b.name AS ‘Source Database’
, b.create_date AS ‘Database Create Date’
FROM sys.databases a
INNER JOIN sys.databases b
ON a.source_database_id = b.database_id
This is what the results will look like.
Microsoft has some good documentation on Snapshots. Go here to read it.
How to Create the Snapshot in SSMS
This is pretty easy to do. Many times if you want to do something with a database you can right click on the database and easily perform the desired task. This could be completing a backup, shrink the database and restore the database. One task that is not present is Create Snapshot. Given this, you will need to use TSQL to create the snapshot. Note the file extension, it is not MDF or NDF.
How to Create the Snapshot in Azure Data Studio
The option to use TSQL is always present, even in Azure Data Studio. And just as in SSMS, when you right click on the database, Create Snapshot is not an option. This is where the DB Snapshot Creator extension can help. Something to keep in mind as we go over this extension, it is still in preview mode. Neither the documentation in Azure Data Studio nor the GitHub site have a great deal of documentation on this. However, there is an FAQ that answers three questions. These questions are below and were taken directly from the extension documentation.
To install the extension you will first need to download the .vsix file. When viewing the extension the in the Extension Marketplace, you can click the green Install button. This will take you to the Github where you can download the file.
Make sure the place this file in a location where it will not be deleted. Once downloaded, you can go to the File menu and to “Install Extension from VSIX package”.
Then simply find the file and it will install. Now when you right click on a database you will see a new menu item to create the snapshot.
By default, when you click the above menu item Azure Data Studio will write the code to create the extension, but not actually create it. The CREATE DATABASE statement earlier in this post was created by this extension. This code is also below.
However, if you would like to skip the step of having the code written, you can go to preferences and uncheck the box below.
While in SSMS, the snapshots are located in a folder as seen below.
In Azure Data Studio, the snapshots are located at the same level as the rest of the databases.
This extension makes the creation of a database snapshot very easy! And like all other extensions, it has a great price. FREE!!
Thanks for visiting my blog!!