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.

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

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.

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.

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 (http://blog.SQLAuthority.com)

About these ads

57 thoughts on “SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

  1. Thanks pinal for the article. I use the sanpshot feature in OLTP environments. Situations where one needs to do a large update, I usually create a snapshot of the original db prior to the update being applied. In case the large update messes up the original db, I have the snapshotdb to get back my original data.

  2. Hi Pinal,

    Thanks for this article, and for your blog in general !
    We use database snapshots with mirroring, because it seems to be the only way to access the mirrored database as this database remains in restoration mode all the time.
    So, with a SQL job, we regularly recreate a database snapshot to use it with Reporting Services.

  3. Hi Pinal ,
    I am facing problem while creating Snapshot Of My DB
    Scenario–>
    –My Sql SERVER Is On Network Drive named ‘\\xeon-s2′
    –My DbId =630
    SELECT physical_name FROM master.sys.master_files WHERE database_id = 630 AND FILE_ID = 1
    –Query returns F:\Rbhargava_CI\Rbhargava_FV_CI.mdf

    CREATE DATABASE Rbhargava_FV_CI_SNAPSHOT ON
    (Name =’Rbhargava_FV_CI’,
    FileName=’\F\Rbhargava_CI\rk.ss’)
    AS SNAPSHOT OF Rbhargava_FV_CI;
    –Error:
    Msg 5014, Level 16, State 3, Line 1
    The file ‘Rbhargava_FV_CI’ does not exist in database ‘Rbhargava_FV_CI’.

    • modify u r qury as

      CREATE DATABASE Rbhargava_FV_CI_SNAPSHOT ON
      (Name =’Rbhargava_FV_CI’,
      FileName=’F:\Rbhargava_CI\rk.ss’)
      AS SNAPSHOT OF Rbhargava_FV_CI;

      and try it out. I hope it will work.

      Regards
      Ramesh

  4. Hello sir,
    Can you put a article on how to take care of incremental data on RegularDB(Example Db). with added data to RegularDB, how we can keep snapshotDB updated. it it is job then what will be steps…or what either way is available…. Thank you in advance for you kind help ….you are always great ..Sql Guru

  5. So I am getting an error message after the snapshot DB is created:

    Executed as user: XXX. Failed to update database “XXX_Reporting” because the database is read-only. [SQLSTATE 25000] (Error 3906). The step failed.

    The snapshot is successfully created but it causing the job to be in a failed state. Any ideas?

    USE [master]
    GO
    /****** Object: Database [XXX_SIT_Reporting] Script Date: 10/04/2010 08:36:26 ******/
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N’XXX_SIT_Reporting’)
    DROP DATABASE [XXX_SIT_Reporting]
    GO
    /****** Object: Database [XXX_SIT_Reporting] Script Date: 10/04/2010 08:35:57 ******/
    CREATE DATABASE [XXX_SIT_Reporting] ON
    ( NAME = N’XXX_SIT’, FILENAME = N’E:\MSSQL\Data\XXX_SIT_Reporting.ss’ ) AS SNAPSHOT OF [XXX_SIT]
    GO

  6. We just started using Snapshots here at work, and they are great. Currently, we have a Job that takes a snapshot of the databases prior to our nightly Full Backup. This serves two purposes for us. First, it allows us to have a pretty recent, already mounted, copy of the database that is ready to go in case we need to restore some data, such as when a careless developer ‘accidentally’ drops a table. Secondly, it allows for our support team to be able to use the Snapshot copy for investigative purposes when issues arise, rather then having to drag down performance on the production database. Since it is Read-Only, I don’t have to worry about them altering any live data. I am also looking at moving our heaviest reports to run on Snapshots, so as to remove the stress these reports generate from our main databases.

  7. I am trying following but it is giving me an error

    — Create Snapshot Database
    CREATE DATABASE SnapshtDB ON
    (Name =’SandBox’,
    FileName=’c:\SSDB.ss1′)
    AS SNAPSHOT OF SandBox;
    GO

    I get the following error

    Msg 5127, Level 16, State 1, Line 2
    All files must be specified for database snapshot creation. Missing the file “SandBox2″.

  8. Dear Sir

    Today i was trying to attach the (MDF,NDF,LDF ) sql server 2008 database which i have received from my client. After attachment the database status is showing (Read-Only) (Eg.database name (Read-Only). How do i make to normal mode for the data updation. is there any query available to resolve this problem. Your help will be highly helpful.

      • Dear Sir

        Thank you for the above query. I tried the query but i was getting the below error

        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Data.mdf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_1_Data.ndf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 9, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_1_Data.ndf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Data_E.mdf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 9, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Data_E.mdf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Data_F.mdf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 9, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Data_F.mdf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Log.ldf”. Operating system error 5: “5(Access is denied.)”.
        Msg 5120, Level 16, State 101, Line 1
        Unable to open the physical file “D:\DATABASE\NAVISION_Log_e.ldf”. Operating system error 5: “5(Access is denied.)”.
        Msg 945, Level 14, State 2, Line 1
        Database ‘NAVISION’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
        Msg 5069, Level 16, State 1, Line 1
        ALTER DATABASE statement failed.

        I have the same operating system where the backup was taken. only the difference was the RAM. (16gb was the orignal and i am restoring the database into 4gb ram) and database size 239gb.

        can you please help me.

  9. Pingback: SQL SERVER – Making Database to Read Only – Changing Database to Read/Write Journey to SQLAuthority

  10. Hello Pinal.

    I was thinking about using the snapshot database for reporting purpose..but it is read only and dont get updated when live database is updated..is there other option where we can create a copy of the live database and updated the copied database whenever live database gets updated… please advice!..Thanks…

    • Snapshot are read only. To create a near time reporting database look into log shipping. Your target database can be read only and in continuous recovery, so the latest transactions are applied.

  11. 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.

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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?

  17. 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

  18. 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)??

  19. 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.

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  21. 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.

  22. 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.

  23. 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.

  24. “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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s