Introduction

"A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space." Technet

Database snapshot is a copy of the database without the transaction log.

Database Snapshot as an online backup

Basically a database snapshot is like a database online read-only backup. In the technet link above, in the benefits section, it says that database snapshot can be used to protect the data from user or administrative error.  For example, in an event when somebody may have deleted a table, you can just copy the table from your database snapshot. The same is true if somebody may have updated the entire table by accident, you can basically restore the updated data from the most recent database snapshot.

Database Snapshot And Business Intelligence

One of the most important item I'll take for Snapshot is that  it can be used for reporting. Let me put that benefit in steroids. Let say i have a call center data for more than 30 thousand agents around the globe. And I have a very large database that takes in data from multiple PBX around the globe. The data is enormous. The problem comes in when the reports starts running. When the report starts running it will put a lock on all the tables it using and it wont go away until the report user are done with it.

With the introduction now of the database snapshot, I can now create a database snapshot on hourly basis and have my reports pointed into those snapshot depending on which country am i building the reports.

Another strategy that you we can make use is that we can now strategize our hardware use. For example for the data warehouse that will take in the load (inbounding data),
We can now make use of RAID array that is optimize for write. While the snapshot that will be used for reporting can be placed on RAID arrays that are optimize for reading data.

Spoiler.

Snapshot is supported Only in SQL Enterprise Edition/Data Center Edition.
There no easy way of doing this in management studio using point in click. You can only do this using TSQL.

Step by Step Guide on Creating Snapshot

Lets do an step by step walkthrough on creating a database snapshot.

Step 1.  Create a database

Lets create a database using this script.

CREATE DATABASE [testdb]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'testdb', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQL2K12R2\MSSQL\DATA\testdb.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'testdb_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQL2K12R2\MSSQL\DATA\testdb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Step 2. Create a Table to Play With

 Create a table and insert data to play with.

CREATE TABLE [dbo].[demo1](
 [demokey] [int] IDENTITY(1,1) NOT NULL,
 [demodata] [varchar](50) NULL,
 CONSTRAINT [PK_demo1] PRIMARY KEY CLUSTERED
(
 [demokey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 3.  Insert Data to play with

    To experiment with this feature you must insert data through management studio.

Step 4. Create database snapshot.

Lets create the snapshot using the script below.

CREATE DATABASE snapshot_testdb
    ON
    (
        NAME = testdb,
        FILENAME = 'c:\testdb\testdb.mdf'  ---< weee no log file
    )
    AS SNAPSHOT OF
testdb

 

Step 5. Locate your SnapShot in Management Studio

Lets locate the snapshot in management studio.

 

Step 6.  Play with the data again..

Play with your sample Data. Do insert or update


Step 7. Create the second Snapshot

Create the second snapshot using the script below:

CREATE DATABASE snapshot_testdb2
    ON
    (
        NAME = testdb,
        FILENAME = 'c:\testdb\testdb.mdf'  ---< weee no log file again
    )
    AS SNAPSHOT OF
 testdb

Step 8 Check for the Second Snapshot in the management Studio

Lets see  how it looks like in the management studio.



Step 9. Compare your playwith Data

To better understand your snapshot Compare your playwith data in the snapshot.