Snapper

I created this SP for a school project. Thought others might benefit from it, so I'm contributing it to the community

The SP takes a snapshot of the specified (db_name) database.

By default, it places all snapshots in the c:\temp\backup directory. That directory must exist prior to invoking this SP. You can override this default by specifying the snap_dir input paramter.

The procedure is designed to be invoked any number of times; however, it only keeps the last three snapshots of the specified db_name. You can adjust this by modifying the snap_count local var.

The name of the 3 snapshots = <db_name>snap<1-3>

If all three snapshots exist, the oldest will be dropped and is retaken.

Here's an example flow. The 'first' time this SP is called with a specified db_name of 'FOO', a new snapshot called 'FOOsnap1' is taken. The 'fourth' time this SP is called with a specified DB name of 'FOO', the snapshot with the name 'FOOsnap1' will be dropped and re-taken. The fifth time, 'FOOsnap2' will be dropped and retaken, etc.

A DB admin shouldn't delete any of the three snapshots. But if s/he does, the SP won't be thrown off; however, the 3 will be out of sync and the DB admin will have to visually inspect all three to find the latest. A
simply query like the following will provide the needed info.

select name, create_date from sys.databases d where d.name like 'FOOsnap%'

Required Input Parameters

db_name: the name of the database to take a snapshot of

Optional Parameters

snap_dir: The directory or folder that is to contain the snapshots.
The default is c:\temp\backup.
**** NOTE: this directory must exist! *****