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:
I hope this helps!
/***************************************************************
UPDATE: Added a check for xp_cmdshell. If it is disabled,
the SP will enable it and then disable it again at the end
***************************************************************/
1 comment:
Awesome, thank you was just what I needed!
Post a Comment