Click here to Skip to main content
Click here to Skip to main content
Go to top

Database Snapshot in MS SQL Server

, 14 Jun 2011
Rate this:
Please Sign up or sign in to vote.
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.

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.

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.

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.

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.

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.

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.

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.

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)

Share

About the Author

Keshav Singh
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

 
GeneralUpdating the snapshot Pinmemberalbsilva_m2u15-Jun-11 4:31 
GeneralRe: Updating the snapshot PinmemberKeshav Singh15-Jun-11 6:04 
GeneralMy vote of 5 Pinmemberaeternam14-Jun-11 22:37 
GeneralRe: My vote of 5 PinmemberKeshav Singh15-Jun-11 8:20 
GeneralMy vote of 5 PinmemberMember 767904114-Jun-11 16:56 
GeneralSnapshot time and performance PinmemberMassimo Conti14-Jun-11 11:53 
GeneralRe: Snapshot time and performance PinmemberKeshav Singh14-Jun-11 20:31 
The answer is 1-2 seconds. I say so because as I have explained in the article initially the snapshot is nothing but the pointer to that actual database. Hence the snapshot while creation simply creates a files which point to the actual database.
 
EXAMPLE:
 
I have a database of arround 75 GB with 2 primary .MDF files in
C:\MSSQL10\MSSQL\Data\TEST.mdf
D:\MSSQL10\MSSQL\Data\TEST2.mdf
 
To create a snapshot:
 
CREATE DATABASE TEST_SS
ON
(NAME=TEST,
FILENAME='C:\MSSQL10\MSSQL\Data\TEST_SS.ss'),
(NAME=TEST2,
FILENAME='D:\MSSQL10\MSSQL\Data\TEST_SS1.ss'
)
AS SNAPSHOT OF TEST
 
The snapshot was created in 1 second. At the later point of time when there is any change in the TEST.mdf or TEST2.mdf the original page files will be written in the TEST_SS.ss and TEST_SS1.ss respectively.
 
I hope I have been able to clarify..
GeneralRe: Snapshot time and performance PinmemberMassimo Conti15-Jun-11 2:43 
GeneralRe: Snapshot time and performance PinmemberKeshav Singh15-Jun-11 4:22 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 14 Jun 2011
Article Copyright 2011 by Keshav Singh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid