Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Comprehensive Backup Script

By Simon Facer,

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 ...)

Total article views: 3323 | Views in the last 30 days: 23
Related Articles

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue


Differential Backup Size

Differential Backup Size


Restore multiple Differential backups

Restore multiple Differential backups


Differential Backup

Quick question on differential backup


Differential backup in 2005

Differential backup in 2005


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones