Database Snapshots

This post is a work in progress. I will edit once I'm home and not
sending these in via email

In short, I think using database snapshots coupled with unit testing
could help alleviate my challenge with database testing in that it
changes the state of the sucker as you're testing. Transactions are
limited in that the changes only exist within the context of the
current thread. Snapshots seem like they'll allow for a persisted
transaction. Once everything is validated, I can roll it back and
have not altered the state of the database. That's my theory at
least. TODO: see whether identities remain incremented ala
transaction or if it really gets rolled back.

I love TSQL but my coworkers don't share my zeal so I want to simplify
the creation of a database snapshot. I use the database created in an
earlier post, SCHEMAD in this example. If I print out the sql
statement I build up, it looks right. If I copy the output and run
it, it works fine. However, if I have the script execute it, it blows
chunks with the error about not locating it in sysdatabases

-- http://msdn.microsoft.com/en-us/library/ms175876.aspx
DECLARE @sql nvarchar(max)
SELECT
@sql = 'CREATE DATABASE ' + DB.name + '_snapshot ON ' + char(10)
+ '( NAME = ' + MF.name + char(10) + ', FILENAME = ''' +
replace(MF.physical_name, '.mdf', '.snapshot')
+ '''' + char(10)+ ')' + char(10) + ' AS SNAPSHOT OF ' + DB.name
FROM
sys.master_files MF
INNER JOIN
sys.databases DB
ON DB.database_id = MF.database_id
WHERE
MF.data_space_id = 1
AND MF.type_desc = 'ROWS'
-- create filter here to identify what database you want a snapshot of
AND DB.name = 'SCHEMAD'

PRINT @sql

EXECUTE @sql

/*
CREATE DATABASE SCHEMAD_snapshot ON
( NAME = SCHEMAD
, FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\SCHEMAD.snapshot'
)
AS SNAPSHOT OF SCHEMAD
Msg 911, Level 16, State 1, Line 19
Could not locate entry in sysdatabases for database 'CREATE DATABASE
SCHEMAD_snapshot ON
( NAME = SCHEMAD
, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL'. No entry
found with that name. Make sure that the name is entered correctly.
*/