Saving Time with Database Snapshots

Recently I ran into an issue where I needed to troubleshoot an ETL process that I was not very familiar with.  After reading through the process to familiarize myself with it, I set up an equivalent (near) environment for my testing.  While restoring the databases only took about 3 hours, I had some pressure to get this issue resolved.  I didn’t want to waste any time resetting the environment while I was debugging, so I decided to use a database snapshot on the target data warehouse to facilitate minimal reset time.  After a quick glance in BOL to verify that this would satisfy my needs, there it was!

Managing a test database

In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data at the start of each round of testing. Before running the first round, an application developer or tester can create a database snapshot on the test database. After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.

This is precisely what I was looking for.   The database snapshot mechanism allowed me to perform test runs of the ETL and quickly reset the target data warehouse back to the state from the beginning of the test.  The scripts are pretty straight forward:

Create the database snapshot

USE [master]

GO

CREATE DATABASE OriginalDatabaseName_Snapshot--name this whatever you want, representative of your snapshot
ON
    (
    NAME=N'OriginalDataFileName',
    FILENAME=N'c:\DataDirectory\OriginalFileName.ss'
    )
AS SNAPSHOT OF OriginalDatabaseName

Restore from a database snapshot

USE master;

RESTORE DATABASE OriginalDatabaseName FROM
DATABASE_SNAPSHOT='OriginalDatabaseName_Snapshot';

GO

This is very useful for debugging, and is also a great way to build automated tests that rely on a uniform dataset to start each test.  There are some performance and growth considerations, this is an excellent way to save some time and ensure a reliable test.

More information about database snapshots:

 http://msdn.microsoft.com/en-us/library/ms175158.aspx