12
OctWebinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
What do you do when you want to capture a particular moment in your life? You take a picture. Once the picture is taken, irrespective of whatever happens to the surrounding environment, that picture remains as it is. Similarly, if you want to take a picture of a database, you can go for a database snapshot. The only difference is that when you are taking a picture in real life, you can take as many as you like. In case of database snapshots, you are only limited to ten only.
Snapshot thus is a recent copy of the table from the database or a subset of rows/columns of a table. The SQL statement that creates and subsequently maintains a snapshot normally reads data from the database residing server. A snapshot is created on the destination system with the create snapshot SQL command.
Over the next few paragraphs we will take you through the different aspects of snapshots, its definition, its use, difference between back up and snapshots and its drawback if any.
Learn SQL Server in the Easiest Way
A database snapshot is a read-only, static view of a database in an SQL Server instance. The database from which you create a snapshot is called a source database. A snapshot captures the state of the database at the moment when it is created. In SQ L Server it is created within Database Snapshots tab under Database tab. It has a small camera icon beside it and is marked as read-only.
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
When you create a snapshot of a source database, the snapshot is empty:
However, when you modify the source database, SQL Server copies the changes to the snapshot. In other words, the size of the snapshot grows as you make changes to the source database:
We have learned what are database snapshots and also its features. Next we are going to learn how to create one.
To create a database snapshot, following are the command:
Let us dissect the query:
As soon as we run the above query it creates a snapshot called db_testsnapshot under the database snapshots section of the database.
As already mentioned snapshot is a read-only copy of the database at a particular moment it consists of all the details of the database at a particular moment but in a read-only mode.
Since the snapshot that we created is a read only version of the database, we can run any normal select statement against the snapshot. But we would not be able to run any insert update or delete statement against the snapshot. Let us take a look.
Let us first run a select statement against the snapshot.
use [db_testsnapshot] select * from [dbo].[emptst]
The output is as below.
But if we want to insert a record in the snapshot it throws an error. Following is an example of that.
The query is as follows
use [db_testsnapshot] insert into [dbo].[emptst](empid,name) values(1,'abc')
Following are the error
Another aspect of snapshot is that after creation of the snapshot, no matter what changes you make in the actual database, it will not reflect in the snapshot. Let us check out the feature with an example.
We have created the snapshot from a database called db_test. Before doing anything let us run a select statement on table emptst for both snapshot and the database.
use [db_test]
select * from [dbo].[emptst]
use [db_testsnapshot]
select * from [dbo].[emptst]
Now let us make some changes in the emptst table of the database db_test.
use [db_test]
insert into [dbo].[emptst] values (5,'Test')
The output is as below.
Now let us check out the snapshot and see if the change is reflected there.
use [db_testsnapshot]
select * from [dbo].[emptst]
The output is as below.
As evident there is no changes reflected in the snapshot database.
You can delete a snapshot in two different ways.
drop database [db_testsnapshot]
SQL Server Training & Certification
Sl no |
Backup |
Snapshot |
1 |
Backup can be stored in another location, same server, or even the same drive in this case. |
Snapshots can be stored only in the same location where original data is present. |
2 |
Backup may have differences based on when the backup started and ended |
Snapshot is a picture of your server at present period of time. |
3 |
It is a long term process and might take a lot of time to complete the procedure. |
It is a short term process and takes very less time, as compared to backup, to complete the procedure. |
4 |
Backup comprises of only file system. |
Snapshot comprises of different types of systems like the files, software ,and settings of that type. |
5 |
Backup takes long time to copy data. |
Snapshot takes very less time to copy the data. |
6 |
In case of SQL Server, backup is a paid service. A person can use this service to automatically backup most recent date every night |
In case of SQL server, snapshot is a free feature that is used to manually create duplicate images of the servers. One can easily generate them at any time. |
7 |
Backup is not a backup unless the data exists in several distinct locations. Backups should be easily and quickly restored. Backups should be verifiable. |
Snapshot by itself is not a backup. But it can be used as an essential part of the backup process. Snapshot is used as a part of the data movement process to a backup file. It is removed when the backup job is completed. |
Over the last few paragraphs, we have given you an introduction about snapshots, its features, its pros and cons and also its difference with backup. Hope this would give you a head start to understand the different aspects of snapshots and its importance as far database is concerned. Hope this would help you further in your advance studies on this topic.
I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Interviews