SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

Snapshot database is one of the most interesting concepts that I have used at some places recently.

Here is a quick definition of the subject from Book On Line:

A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.

If you do not know how Snapshot database work, here is a quick note on the subject. However, please refer to the official description on Book-on-Line for accuracy. Snapshot database is a read-only database created from an original database called the “source database”. This database operates at page level. When Snapshot database is created, it is produced on sparse files; in fact, it does not occupy any space (or occupies very little space) in the Operating System. When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases. When an unmodified data page is read in the Snapshot database, it actually reads the pages of the original database. In other words, the changes that happen in the source database are reflected in the Snapshot database.

Let us see a simple example of Snapshot. In the following exercise, we will do a few operations. Please note that this script is for demo purposes only- there are a few considerations of CPU, DISK I/O and memory, which will be discussed in the future posts.

  • Create Snapshot
  • Delete Data from Original DB
  • Restore Data from Snapshot

First, let us create the first Snapshot database and observe the sparse file details.

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO

Solarwinds

Now let us see the resultset for the same.

SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot1

SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot2

SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot3

Now let us do delete something from the Original DB and check the same details we checked before.

-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot4

When we check the details of sparse file created by Snapshot database, we will find some interesting details. The details of Regular DB remain the same.

SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot6

It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database. This is the reason why the size of the snapshot DB is increased.

Now let us take this small exercise to  the next level and restore our deleted data from Snapshot DB to Original Source DB.

-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

Now let us check the details of the select statement and we can see that we are successful able to restore the database from Snapshot Database.

SQL SERVER - 2008 - Introduction to Snapshot Database - Restore From Snapshot snapshot1

We can clearly see that this is a very useful feature in case you would encounter a good business that needs it.

I would like to request the readers to suggest more details if they are using this feature in their business. Also, let me know if you think it can be potentially used to achieve any tasks.

Complete Script of the afore- mentioned operation for easy reference is as follows:

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Enable Identity Insert – Import Expert Wizard
Next Post
SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database

Related Posts

68 Comments. Leave new

  • 2)how to find out where the sanp shot file stores
    3)If i have multipul files exist for a database in a different drives then how to findout for which file snap shot is created.
    4)how to find out snap shot file size
    5) how to findout how much size is required for a snap shot when check db runs on the database.

    Reply
  • Hi Pinal,

    I need to take a snapshot of one database and restore into other database. Is it possible to do like that.

    Please reply me fast.
    Thanks in advance.

    Reply
  • “As the source database is updated, the database snapshot is updated.” – this is totally brain dead! Perhaps useful as an option but if this is forced on you then it is not a snapshot!!! I don’t want the snapshot to be updated!

    Reply
  • Hi Pinal,

    Could you please let us know whether we can create a snap shot of a single table? Will that be possible..

    Reply
    • Not possible. DBAs normally used “Select … Into” to create a new table with the data and structure of old table.

      Reply
  • Pinal, Hi!
    one question….i need to name one snapshot like original database, do you know some query to do this?
    Tks

    Reply
  • Is there any dis advantage of snap shot? any performance issue of database?

    Reply
  • Thanks for the article, I have one doubt..
    I will create one database snapshot…after creating snapshot i will do some modification in original database.
    Will that be reflected in snapshot which i have created before modification?

    Reply
  • Can anyone explain this? I’m confused by the concept.

    “It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database.”

    It looks like the concept states that when the data pages get modified on Sources database, it copies the data pages to Snapshot. But the Snapshot Database remains the same when I select a table from Snapshot database.

    Now I saw many people ask this question, but no one have answer. This concept is conflict.. Can someone help to explain this?

    Reply
    • Lets say there is a page 200 which is having a row. When I create snapshot and query the table in snapshot database, the page would be read from .MDF file of original database. Now, if the page is modified then old copy would be pushed in .ss file on snapshot data and would be read from there whenever we query snapshot database.

      hope its clear.

      Reply
  • Good introduction

    Reply
  • Hi Pinal.. Its a nice article. I have couple of doubts here. What happens to the fresh data coming in your original database after you created snapshot? Will that data be pushed to snapshot too?
    What if i have three snapshots of a single database, if i make any modification in source database, will all three snapshots be updated or the recent one? Thanks.

    Reply
  • Aakash Suryawanshi
    June 10, 2019 12:13 pm

    Hi Pinal,

    Can a database snapshot be used as a default database for a login ?

    Thanks!

    Reply
  • After creating the snapshot, I can i use this snapshot to standup a DB instance on a different physical machine?

    Reply

Leave a Reply

Menu