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

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)

,
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

  • Gr8 article. One thing I ran into when created a snapshot during upgrade was performance impact. I thing this sparse file becomes so busy when too much is going on with db that DB went extremly slow. Since than I have stopped using it during upgrades for backup.

    Reply
  • HI
    I HAVE DATABASE ‘X’ ON SQLSERVER 2005
    I BACKED IT UP AND RESTOR IT ON SQL SERVER 2008 R2 WITH NO RECOVERY OPTION

    I USED THE NEW RESTORED DATABASE AS A MIRROR DATABASE

    WHEN I WANT TO CREATE A SNAPSHOT OF THIS MIRROED DATABASE

    AN ERROR SHOW UP
    (CANNOT OPEN DATABASE ‘X_SNAPSHOT’ VERSION 611, UPGRADE YOUR DATABASE TO THE LATEST VERSION)

    THANKX

    Reply
  • Dave,
    The online book says:
    When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189940(v=sql.105)

    Reply
  • Hi,

    can configured snapshot like mirroring in sql server.i want every 1 hr to create snapshote on sql server and drop exiting snapshot.database size is 20 gb.
    It is possible to configure above environment.

    Thanks
    Mangesh

    Reply
  • In First Post
    >> When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases.

    >> In other words, the changes that happen in the source database are reflected in the Snapshot database.

    Which data? modified or original data page get copied to snapshot?
    My understanding is original page copied to snapshot.

    Regards

    Reply
  • Pinal

    When I do this in 2008 R2, I see the size of the snapshot the same as Original DB Size. What exactly am I doing wrong?

    Thanks

    Reply
  • Hi ,
    I tried that snapshot.It shows the following error

    Msg 5070, Level 16, State 2, Line 1
    Database state cannot be changed while other users are using the database ‘RegularDB’
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    What is the solution?

    Reply
    • It means that RegularDB is currently being used by some other users. You need to disconnect all users before trying the code

      Reply
  • can we use database snapshots for reporting purposes.
    Because on live productions databases querying tables causes sometimes locks.
    Is it a good idea using for reporting?
    Thanks

    Reply
  • Hi Pinal

    Am new to this concept and i have doubt regarding this post.. While creating the snapshot , the filename is given a path (FileName=’c:\SSDB.ss1′).. What does this path stand for?? Is it the path ofthe DB that you created(RegularDB)??

    Reply
  • 1 gulab jamun for you, great tip

    Reply
  • how can i restore sql server 2008 database to sql server 2005 script is not genrating of database

    Reply
  • Hi Pinal,

    Will Snapshot database be updated at regular intervals or not ?

    Reply
  • Rodney Overcash
    February 7, 2013 9:43 pm

    I love this! We have a test server where I test application changes. This would be perfect for reseting the database back to the original values to rerun a test in seconds! Plan to try that.

    Reply
  • Deen Dayal Dinkar
    February 19, 2013 8:52 am

    Hi Pinal,
    Please write another uses of Snapshot

    Reply
  • Raghawendra Shukla
    February 19, 2013 8:54 am

    Great Article….

    Reply
  • Sherihan Anver (@amsherihan)
    April 18, 2013 8:09 am

    This post helps to understand the basics of database snapshots in a clear view. Really helpful.

    Reply
  • Patrick (@Patriarck)
    April 25, 2013 5:42 am

    I’m creating snapshot DB from a mirrored DB, but in some cases it takes more than 15 minutes to create the snapshot. any idea why this could happen ?

    Reply
  • Hello Pinal,
    I found your instructions for scripting a backup. When snapshots are used, the dabase will not detach. If you remove a snapshot, it cannot be re-attached. Also, when doing a backup (.BAK), snapshots are not captured. This vulnerability is having us rethink the use of snapshots. They work great in the reports we use them for, but would leave us in a bad spot if we ever had to do a recovery from .BAK.

    Reply
  • great Article Pinal, Thanks :)

    Reply
  • Hello Pinal,

    I have few questions :
    1)where the snap shot files stores
    2)

    Reply

Leave a Reply

Menu