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

http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

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

April 5, 2010 by pinaldave

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
GOUSE 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
GORESTORE 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
GOUSE 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
GORESTORE 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

http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-database-snapshots.aspx

Here are some notes on “SQL Server 2008 Database Snapshots” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).
Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

---

Database Snapshots

• Read-only, static views of an entire database
• See http://msdn.microsoft.com/en-us/library/ms175158.aspx
• Used for: Reporting, protection from user error, major updates, unit testing
• Careful – When using with major updates or bulk operations, consider the impact
• Uses NTFS sparse files, always in the same server as database
• CREATE DATABASE … ON (FILE=Name, FILENAME='...') AS SNAPSHOT OF …
• See http://msdn.microsoft.com/en-us/library/ms175876.aspx
• File specified under FILENAME contains changes in the original database since snapshot time
• Can’t drop, detach or restore database (or add more files to it) when a snapshot exists
• Careful - If you lose the original database files, you lose the snapshot 

Considerations 

• Can create multiple snapshots of the same database
• Careful – Additional write workload when updating original database with multiple snapshots
• Can’t use with master, model or tempdb
• Can’t change permissions on snapshot after it is created
• No option to refresh a snapshot. Need to drop and recreate. 
• If creating on mirrored database, the mirror needs to be synchronized
• Created always with ALLOW_SNAPSHOT_ISOLATION set to ON

Demo

• Create a database, create a table, insert a few rows
• Create snapshot with CREATE DATABASE … AS SNAPSHOT OF …
• Query the table in the snapshot
• In Windows Explorer, at "Size" and "Size on Disk" properties of the file
• Go back to original database, insert a few more rows, update some rows, delete some rows, query the table
• Query the table in the snapshot - verify it has the old state
• In Windows Explorer, at "Size" and "Size on Disk" properties of the file again