Michael J. Swart

March 26, 2009

Using Database Snapshots as Transactions

Filed under: SQL Scripts,Technical Articles — Michael J. Swart @ 7:17 am

Linchi Shea recently asked whether people were using database snapshots or not. The consensus seems to be that they’re used during testing and rarely in production.

I use them for testing as well. They’re very good when used this way. Being able to revert to an snapshot is what makes the feature useful. It’s not that hard to understand really. It’s very very much like a database transaction.
Here’s the template that I use, just cut and paste into management studio and type Ctrl+Shift+M in order to replace the template values. It’s not quite foolproof, you do need to understand snapshots and edit the script to specify data file names. But maybe you’ll find it useful.
-- "begin tran"
       declare @cmd nvarchar(max)
       select @cmd = N'use <dbname,,> exec sys.sp_helpfile'
       exec sp_executesql @cmd
CREATE DATABASE <dbname,,>_Snapshot ON
NAME datafileFILENAME 'C:\Temp\<dbname,,>_Snapshot.ss' )
AS SNAPSHOT OF <dbname,,>

-- "rollback"
   select spid from sys.sysprocesses where dbid = db_id('<dbname,,>') and spid > 50
USE master
RESTORE DATABASE <dbname,,> FROM DATABASE_SNAPSHOT '<dbname,,>_Snapshot';
DROP DATABASE <dbname,,>_Snapshot;

-- "commit"
DROP DATABASE <dbname,,>_Snapshot

1 Comment »

  1. […] transactions + db snapshots = this script […]

    Pingback by Gleeful Calamity | Michael J. Swart — December 16, 2009 @ 11:23 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress