The script contains 3 procs, 2 UDF's (one each for 2000 and 2005), and 5 jobs (created as disabled). To execute this script, you must add the Backup Folder and Log Folder values to the top of the script, else the jobs wont be created. The procs (and UDF) are created in master, hence the use of sp_ as the prefix.
This complete script is 1800 + lines long ...
The procs are:
(1) sp_PathExists - this will verify and create the specified folder path
(2) fn_DatabaseDetails - this will get the DB metadata for the server, two UDF's are included, one for 2000 and one for 2005, implemented as dynamic SQL
(3) sp_DatabaseBackup - this does the work.
(4) sp_DeleteFiles - this proc does the clean-up work, to delete old backups.
(5) Jobs - 5 jobs are created -
File Cleanup
System DB - Full Backups
User DB - Differential Backups
User DB - Full Backups
User DB - Transaction Log Backup
The jobs are spec'd to our generic / basic backup strategy (weekly Fulls (Friday, @ 6pm), nightly Differentials (nightly @ 6pm, except Friday), daily T-Log @12:00am. Obviously, this is customized per server as required.
sp_DatabaseBackup has intelligence built in - A Full backup will be run if a Differential backup was requested, and there is no prior Full, T-Log backups won't be attempted on any DB in Simple recovery mode, etc
.
The objects (procs and UDF's) are well commented as to the parameters for each, but if you run the whole script, you can check out the jobs that are created for examples on how to run this process.
Enjoy ... and if you find any errors, or improvements, please let me know (and the "Comprehensive" in the title is a little tongue-in-cheek, there are lots more options that could be included - LiteSpeed, Marked Transactions to name but two ...)
