SQL Server Database Snapshot

Article
1/17/2024

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, we can just copy the table from our database snapshot. The same is true if somebody may have updated the entire table by accident, we can basically restore the updated data from the most recent database snapshot.

Database Snapshot And Business Intelligence

One of the most important item we'll take for Snapshot is that it can be used for reporting. Let's put that benefit in steroids. Let's say we have a call center data for more than 30,000 agents around the globe. And we 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, we can now create a database snapshot on hourly basis and have reports pointed into those snapshot depending on which country building the reports.

Another strategy that 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. We can only do this using T-SQL.

Step by Step Guide on Creating Snapshot

Let's do a step by step walkthrough on creating a database snapshot.

Step 1. Create a database

*Let's 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.

Let's 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 our SnapShot in Management Studio

Let's locate the snapshot in management studio.

Step 6. Play with the data again..

Play with our 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 management studio

Lets see how it looks like in management studio.

Step 9. Compare your playwith Data

To better understand our snapshot compare our playwith data in the snapshot.

Share via