Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Database Snapshot in MS SQL Server

Rate me:
Please Sign up or sign in to vote.
4.63/5 (19 votes)
14 Jun 2011CPOL4 min read 75.7K   29   10
An introduction to the database snapshot functionality in MS SQL Server. It is one of the important tools used for generating data for reporting purposes.

Introduction

Smile, say cheese, and click!! And the snap is clicked. Anytime later when I look at it, it reminds me of the time the snap was clicked. I really wish there was a time machine which could take me back in time. Alas, there is no such time machine with a rewind button in life. Not so in databases. Database snapshot is that wished for feature; it makes the life of databases amazing. Snapshots can be considered as a real time point in time restore option which takes the image of a database. Anytime later, this image/snapshot can be used to revert the database to the point the snapshot was taken.

Snapshots would be desirable in a number of situations:

  1. Snapshots are read only and are a very good option for reporting purposes. All the reporting related hits could be made on the snapshot instead of on the the actual database, and the burden on the active database could be minimized. Also, as snapshots are read only, chances of any undesired updates by the subscriber (who reads data for reports etc.) could be negated.
  2. Snapshots could be used to preserve data for financial statistics/analysis. An example yearly snapshot data for a particular product could be used to perform statistical analysis and predict its market growth.
  3. Snapshots could also be used to restore the current database to the point the snapshot was taken, if the need arises. It would perform a very quick point in time restore of the database.

Initially, when a snapshot is taken, it's nothing less than a pointer to the actual database, i.e., when I query the snapshot, it retrieves data from the actual database. But this happens only till the time the data page has not been changed on the actual database. The moment a change is made to the database, the original page is placed on to the snapshot and then the change is made to the page on the database. This makes sure that the snapshot keeps the image intact. Till the time the snapshot is active, the actual database cannot be dropped. To be able to drop the database, the snapshot would have to be deleted first, followed by the database. Snapshot takes very little disk space. The space occupied by the snapshot is nothing but the changed data pages. For unchanged pages, it still fetches the data from the actual database.

Snapshot Creation

Before we create a snapshot, let's create a database, a table, and insert a few records into the table.

SQL
USE master
CREATE DATABASE TEST
USE TEST
CREATE TABLE Student(ID INT,Name VARCHAR(15),SECTION CHAR(1))
INSERT INTO Student VALUES 
(1,'Keshav','A'),
(2,'Sachin','B'),
(3,'Rahul','C')
SELECT * FROM dbo.Student

ID          Name            SECTION
----------- --------------- -------
1           Keshav          A
2           Sachin          B
3           Rahul           C

(3 row(s) affected)

Let's note the size of the database.

SQL
USE TEST
EXEC sp_spaceused
database_name     database_size   unallocated space 
----------------- --------------- ------------------
TEST               1.87 MB        0.11 MB

reserved           data               index_size    unused
------------------ ------------------ ------------- -------
1296 KB            512 KB             640 KB        144 KB

and also the size of the .MDF file at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST.mdf.

Next, we will create a snapshot of the database.

SQL
USE master
CREATE DATABASE TEST_SS
ON
(NAME=TEST,
FILENAME='C:\Program Files\Microsoft SQL Server\
          MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_SS.ss'
)
AS SNAPSHOT OF TEST

The above query creates a snapshot of the test database which is visible on the Object Explorer.

Let's note the size of the database.

SQL
USE TEST_SS
EXEC sp_spaceused
database_name     database_size   unallocated space 
----------------- --------------- ------------------
TEST_SS               1.87 MB        0.11 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
1296 KB            512 KB             640 KB             144 KB

The space used details shown above are of the “Test” database which the snapshot is pointing to, and to confirm the observation, if we go to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_SS.ss and right click for Properties, we will see what is shown below. The actual size occupied by the snapshot is merely a 192 KB.

Next let’s insert a few records into the TEST database’s Student table.

SQL
USE TEST
INSERT INTO Student VALUES 
(4,'Rohit','A'),
(5,'Saumya','B')
Now if we query the snapshot:
USE TEST_SS
SELECT * FROM dbo.Student

ID          Name            SECTION
----------- --------------- -------
1           Keshav          A
2           Sachin          B
3           Rahul           C

(3 row(s) affected)

There is still the same number of records in the snapshot.

Also, let's try to get the size of the snapshot.

We observe that the size of the snapshot has increased; this is on account of the page being copied to the snapshot. The page holding the Student table’s three records got copied into the snapshot when we added the two additional records into it.

Next, let's try to perform an update on the snapshot data.

SQL
UPDATE TEST_SS.dbo.Student SET Name='XXX'
WHERE ID=1
------------------------
Msg 3906, Level 16, State 1, Line 1
Failed to update database "TEST_SS" because the database is read-only.

Any DML is not possible as the snapshot is always read-only.

Next, let’s try to drop the TEST database.

SQL
DROP DATABASE TEST
Msg 3709, Level 16, State 2, Line 1
Cannot drop the database while the database snapshot 
       "TEST_SS" refers to it. Drop that database first.

Restore from Snapshot

As I said earlier, with a database snapshot, we can reverse the database to the point when the snapshot was taken. This can be done by restoring the database from the snapshot.

A snapshot for a database has pages that are being modified; they are copied to the snapshot before they are updated in the original database so the snapshot contains the unmodified page and the original database holds the modified page. Now when we restore a database from a snapshot, the unmodified pages stored by the snapshot are simply copied on the actual database and the transaction log is also overwritten and rebuilt.

Can we perform regular backups with the help of a snapshot? Can we restore the snapshot on a different server? Can we perform copy database functionality on a snapshot database from the host server to another?

The answer to all of the above questions is no. Since the database snapshot is an incomplete copy of the database, it’s not meant for performing any regular backups. It could be considered as a real time point in time restore option.

SQL
USE master
RESTORE DATABASE TEST
FROM DATABASE_SNAPSHOT = 'TEST_SS'

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!

Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

Comments and Discussions

 
QuestionIs time to restore from snapshot less than time taken to restore from BAK file ? Pin
zerocool1822-Apr-15 0:37
zerocool1822-Apr-15 0:37 
GeneralUpdating the snapshot Pin
albsilva_m2u15-Jun-11 4:31
albsilva_m2u15-Jun-11 4:31 
GeneralRe: Updating the snapshot Pin
Keshav Singh15-Jun-11 6:04
Keshav Singh15-Jun-11 6:04 
GeneralMy vote of 5 Pin
aeternam14-Jun-11 22:37
aeternam14-Jun-11 22:37 
GeneralRe: My vote of 5 Pin
Keshav Singh15-Jun-11 8:20
Keshav Singh15-Jun-11 8:20 
GeneralMy vote of 5 Pin
Member 767904114-Jun-11 16:56
Member 767904114-Jun-11 16:56 
GeneralSnapshot time and performance Pin
Massimo Conti14-Jun-11 11:53
Massimo Conti14-Jun-11 11:53 
GeneralRe: Snapshot time and performance Pin
Keshav Singh14-Jun-11 20:31
Keshav Singh14-Jun-11 20:31 
GeneralRe: Snapshot time and performance Pin
Massimo Conti15-Jun-11 2:43
Massimo Conti15-Jun-11 2:43 
GeneralRe: Snapshot time and performance Pin
Keshav Singh15-Jun-11 4:22
Keshav Singh15-Jun-11 4:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.