Whenever I'm writing or executing scripts that might be destructive I of course wrap my code in appropriate transaction control blocks. However, transaction control blocks (BEGIN TRAN/ROLLBACK/COMMIT) may not always be possible if you are running code across .sql files, manipulating foreign keys, setting up replication, needing a custom grain of transaction control, etc. In these cases Database Snapshots work great.
A database snapshot is kinda like a live, queryable copy of a database that is transactionally consistent as of when the snapshot is created. The purpose of this post is not to get into the guts of how database snapshots work. One of the nice features of a database snapshot is that you can RESTORE your live database back to the snapshot with one simple RESTORE command. You probably woouldn't do this on a production database, but it is valuable for dev databases.
However, MS, in my opinion, could've made the CREATE syntax for database snapshots a little bit easier. The big problem is if your database has more than a few db files you have a rather lengthy CREATE DATABASE statement. This stored procedure simplifies the process. You pass it @SnapshotName and it builds the code for you to create a database snapshot. It assumes your "sparse" files will live in the same directory structures as the underlying files. It then creates a RESTORE command that you can run later, if needed, to revert back to your snapshot.
The script will generate output similar to the following...
I use this script all the time in my dev environments. I hope you find it useful too.