Database Snapshot Stored Procedure

We've been trying to tighten up the database release process at work. This involves lots of big and small changes including things like using version control software better, documenting database code as well as putting together some default scripts that run with every release.

It occurred to me that even if you have a good process and set of check lists for the release cycle it is still quite easy to miss out on small but ultimately important steps. Things get hectic, last minute changes are made, etc, etc., and the next thing you know you've forgotten a basic, but important, step like creating a database snapshot or disabling scheduled jobs or revoking user access for the duration of the release. There are probably a dozen other small tasks that can be left out.



As part of this effort to make releases less problematic, I've created the stored procedure below that will create a database snapshot for a specific database. There are three parameters:

• @db sysname - The name of the database to be backed up
• @CheckDriveSpace bit
• 1 = Checks the for the disk with the most space. It will only look for disks that have database files on them already, so the snapshot files won't be placed willy-nilly on the server
• 0 = Will not run a check and will create the snapshot in the same directory as the database being backed up
• @DropPreviousSnapshots bit 
• 1 = Will check for existing database snapshots of the database specified and drop them
• 0 = Will not check for and drop existing database snapshots

As with all code, I did not put this together without any help. The Powershell section was taken and adapted from myharpreet.blogspot.co.uk. And the section of the script that finds a database file's directory came from stackoverflow.com.

The code for the stored procedure is:

/***************************************************************

UPDATE: Added a check for xp_cmdshell. If it is disabled, 

the SP will enable it and then disable it again at the end

***************************************************************/

I hope this helps!