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

Buy Self-learning at Discounted Price

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

Enrol For a Free Demo Class

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

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.