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 = datafile, FILENAME = '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';
GO
DROP DATABASE <dbname,,>_Snapshot;
GO
-- "commit"
DROP DATABASE <dbname,,>_Snapshot
