Using A SQL Server Database Snapshot As A Development Tool

I am modifying a number of SSIS packages that extract, transform, and load data into various tables. While unit testing I need a quick way to refresh my development environment. The traditional method is to take a backup and restore but if the database is large it can take a significant amount of time – even a ten minute restore drags on forever if you have to run it a dozen times a day. Another option is to duplicate the data in separate tables then kill and fill to refresh the data. This can run quickly (depending of course on table sizes, indexes, etc.) but it can be complex to set up. My favorite approach is to create a database snapshot as my backup, run the load, evaluate the results, then restore from the snapshot – quick and easy to set up. (If you are not familiar with database snapshots here is a good overview.)

Quick

Due to their sparse file architecture DB Snapshots are created instantaneously, all that gets created is an empty file. As changes are made to the original the altered 8KB pages are written to the new file with no logging – in fact the snapshot doesn’t even have a transaction log. When you want to restore from the snapshot the copied pages are just copied back to their original location. If your changes are relatively small in terms of 8KB pages compared to the total DB you will find this lightning fast.

Easy

A DB Snapshot is as easy to create as a regular DB. The only complexity is if your DB has multiple files so must your snapshot. Never fear though, I have a code generator that will handle all the gory details, see below. Please be sure you read the comments at the top so you know how to run it. There is also code at the end of the script to restore to the original state as well as to drop the snapshot.

Caveats

Restrictions

  • Snapshots are only available in Enterprise Edition.
  • The Snapshot is Read-Only which shouldn’t be a problem when used only for restores.
  • Creating a Snapshot requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

Size Issues

Normally DB Snapshots don’t take up much space when used this way but be sure to clean up after yourself! Since the snapshot keeps collecting changes if you forget to drop the Snapshot it will continue to grow to the size of the original if enough changes are made.

Exclusive Use

You are not required to have exclusive use except for the restore but if your buddies are using the original DB and you do they restore the may not be your buddies anymore. Remember, with great power comes great responsibility.

 

Code

I apologize for the code formatting issues, WordPress and I are having issues. 😦 

/****************************************************************
This script will write a script to create a snapshot of the source DB, it will
handle multiple files in the DB.  It also supports multiple snapshots of the
same source by changing @SSName.
     
      Set @SourceDBName to the name of the DB you want a snapshot of.
      Set @SSName to what you want to call your snapshot.
     
Ctrl+Shift+M will allow you to enter the DB and Snapshot names.
 
You can enable the Execute statement at the end if you want to build the
snapshot now.
********************************************************/
Declare
      @SourceDBName As sysname
      ,@SSName As sysname
      ,@SQL as Varchar(4000)
Select
      @SourceDBName = ‘<SourceDBName,sysname,Essence>’
      ,@SSName = ‘<SSName,sysname,EssenceSS>’
 
SELECT
      @SQL =
      ‘CREATE DATABASE ‘ + @SSName + ‘ ON ‘
            + LEFT(Script,LEN(Script)-1)
            + ‘AS SNAPSHOT OF ‘ + @SourceDBName
FROM
      (SELECT
            (
                  SELECT col + ‘, ‘ as [text()]
                  FROM
                        (
                              Select Top 100
                                    Case
                                          When Right(mf.physical_name,4) = ‘.ndf’
                                                Then ‘(NAME = ‘ + mf.name + ‘ ,FILENAME = ”’
                                                      + Replace(mf.physical_name,’.ndf’,’_’ + @SSName + ‘.ss’) + ”’)’
                                          Else ‘(NAME = ‘ + mf.name + ‘ ,FILENAME = ”’
                                                + Replace(mf.physical_name,’.mdf’,’_’ + @SSName + ‘.ss’) + ”’)’
                                    End               As col
                              From
                                    sys.master_files  As mf
                                    Inner Join
                                    sys.databases     As db
                                          on mf.database_id = db.database_id
                              Where
                                    db.name = @SourceDBName
                                    And
                                    mf.type_desc = ‘ROWS’
                              Order By
                                    [file_id]
                        ) As A
                  ORDER BY col ASC
                  FOR XML PATH(”)
            ) AS Script
      ) B
 
–Execute (@SQL)
Print @SQL
——————————————————————————————–
–Other useful code
      /*Restore from snapshot**********************************
            Use Master
            Go
            Alter Database <SourceDBName,sysname,Essence>
                  Set  RESTRICTED_USER With ROLLBACK IMMEDIATE
            Go
            Restore Database <SourceDBName,sysname,Essence>
            From Database_Snapshot = ‘<SSName,sysname,EssenceSS>’
            Go
      ******************************************************/
      /*Drop Snapshot*****************************************
            Drop Database <SSName,sysname,EssenceSS>
      ******************************************************/
Advertisements

2 thoughts on “Using A SQL Server Database Snapshot As A Development Tool

  1. This is great. One thing I had to change is adding brackets around the sysnames in the dynamic SQL because some of my names have a dash/space in them. Other than that, I want to thank you for saving me lots of time!

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