RnewYear2022 RnewYear2022

- SQL Server Blogs -

Snapping a Picture of a Database-Database snapshot

Introduction

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

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Snapshot and its Use

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:

How to Create Database Snapshots

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:

  1. CREATE DATABASE db_testsnapshot ON  
  2. ( NAME = db_testsnapshot, FILENAME =   
  3. 'D:\data\db_testsnapshot.ss' )  
  4. AS SNAPSHOT OF db_test;  
  5. GO  

Let us dissect the query:

  1. Since snapshots are read only form of a database we start the snapshot creation with the create snapshot keyword.
  2. We provide the name of the snapshot.
  3. We provide the location of the snapshot physical file.
  4. We provide the details for which database we are creating the snapshot. Here in this case it is db_test.
  5. We run 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.

How to Delete a Snapshot

You can delete a snapshot in two different ways.

  1. You can directly select the snapshot from the object explorer and click on delete.
  2. Use the standard sql query to delete the data.

drop database  [db_testsnapshot]

Database Snapshot Benefits

  1. A database snapshot is convenient, reliable, Read- only, point-in-time copy of the database.
  2. It is very beneficial when doing critical updates to the database as if anything goes wrong database can be restored to that point without any hassle.
  3. If we want to view database suppose at 7 pm, we can create database snapshot and easily query the database, however, without snapshot we have the option to take the database backup and restore using some different name but it requires considerable system resources especially disk space.
  4. Sparse file (snapshot) is small and easy to create however the size of snapshot depends upon the amount of operation and page updated.
  5. Multiple database snapshots are possible for a single database.
  6. There is no dependency for the recovery model; it works with full, bulk-logged and simple also.
  7. Snapshot creation is really quick; it takes a few seconds only for big databases too.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

Database Snapshot Limitation

  1. A database snapshot can be created onto the source server only. It cannot be moved to another server
  2. We can’t drop an Original Source Database as long as a referring snapshot exists in that database
  3. It cannot be Backed up and also detach \attach doesn’t work with database snapshots
  4. Snapshots can be created for only user databases, not for the master, model and msdb
  5. If we revert to a snapshot log Chain will get broken, thus we have to take a full or differential backup to bridge the log chain sequence
  6. Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress
  7. If the source database is unavailable or corrupted we cannot use the database snapshot to revert it to the original state
  8. We cannot add new users for database since it is a read-only copy
  9. There is no graphical user interface for creating and reverting back the snapshot, this need to be done from query only
  10. There is some overhead for every DML operation as before operation page needs to be moved out
  11. If the drive in which snapshot exists is out of space which causes any DML to fail the snapshot will be in suspect mode and non-recoverable
  12. The full-text index is not available with a snapshot
  13. It is available with enterprise edition only
  14. Database files that were online during snapshot creation should be online during the snapshot revert also

When Not to Use Database Snapshots

  1. It does not substitute for high availability solution such as mirroring, log shipping etc.
  2. Very high user based activity as it might have extra overhead
  3. Replacement of database backup as it is fully dependent on the source database

Difference Between Snapshot and Backup

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.

Conclusion

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.


     user

    Sanchayan Banerjee

    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.


Comments

Related Courses

Trending Courses

salesforce

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53
salesforce

Upcoming Class

-1 day 04 Feb 2023

salesforce

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
salesforce

Upcoming Class

5 days 10 Feb 2023

salesforce

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
salesforce

Upcoming Class

-1 day 04 Feb 2023

salesforce

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
salesforce

Upcoming Class

-1 day 04 Feb 2023

salesforce

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
salesforce

Upcoming Class

6 days 11 Feb 2023

salesforce

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
salesforce

Upcoming Class

-1 day 04 Feb 2023

salesforce

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
salesforce

Upcoming Class

1 day 06 Feb 2023

salesforce

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
salesforce

Upcoming Class

5 days 10 Feb 2023

salesforce

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
salesforce

Upcoming Class

12 days 17 Feb 2023

salesforce

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
salesforce

Upcoming Class

6 days 11 Feb 2023

salesforce

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
salesforce

Upcoming Class

19 days 24 Feb 2023

salesforce

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
salesforce

Upcoming Class

-1 day 04 Feb 2023

Interviews