Monthly Archives: February 2009

Create SQL Snapshot Backup to Document Change Process

Let’s get the disclaimer out of the way right up front.  This article is not intended to be used in place of a Production grade backup solution.  It can be used in conjunction, with your regular backup rotation, but if you truly care about the database and its contents, please, refer to books on-line or read this Introduction to Backup and Restore Strategies in SQL Server.

If you already know all that and want a quick and dirty way to test database modifications or statements in a development environment…proceed. 

While not a developer by trade…there are circumstances which call for me to wear a different hat.  When that happens…I typically have to develop, and document, an entire change process.  No different than “a real developer”.  The steps are then submitted to a change review board, approved, scheduled, and applied to our production environment.

Going in, I have an idea how it will go.  As the process unfolds, undoubtedly something will occur, or reveal itself, that I hadn’t thought of and the documentation changes a bit…with each iteration.

Back in the day…when we had to walk both ways, uphill, in the snow, to school…we had to do a backup to disk, run through the changes, document the process, restore the database…and then do it all over again.  The backup and restore process, with a database of any size, can take a fair amount of time.

Enter “SQL Snapshot Backups” – While the dress rehearsals, today, haven’t changed much…the backup and restore process has.  Instead of it taking minutes or hours…taking a snapshot backup or reverting to a previous one takes seconds.  Since each run-thru is that much quicker…you can focus on getting the process and documentation right.

The syntax below can be used to create a Snapshot of “Adventureworks”.  A couple catches…the NAME is the logical name, of the data file, within the database and the path to the FILENAME, you specify, must exist.

–Create a database snapshot

DATABASE  Adventureworks_snapshot 
 NAME = AdventureWorks_Data
, FILENAME = D:\Backup\Adventureworks_Snapshot’
) AS SNAPSHOT OFAdventureworks


The Snapshot should then be visible in the Database Snapshots folder: snapshot13



Once you have confirmed the Snapshot has been taken you can run through your modifications, document, and verify your process.  Should you need to, use the syntax below to revert back to the state the database was in when the snapshot was initially taken…and run through it again.

 — Reverting AdventureWorks to AdventureWorks_snapshot

from DATABASE_SNAPSHOT = Adventureworks_snapshot’;


When you are comfortable with your results and have the change process fully documented you can use the following to delete the snapshot:


 –To Delete AdventureWorks Snapshot

The following is a link to an article that contains some Best Practices for Creating Database Snapshots if you would like more information.