Simplify SQL Backup Jobs using helper functions and stored procs

Description

The SQL Backup Helper is a set of Stored Procedures and User Defined Functions, which help you automate the process of creating backup sets, cloning database and creating a history set of database backups when using a SQL Server or SQL Server Express editions.

Works with

• SQL Server 2005 or higher
• SQL Server 2005 Express or higher

Features

• Create full backups of selected databases, using wildcards of inclusion and exclusion
• Create differential backups of selected databases, using wildcards of inclusion and exclusion
• Create transaction log backups of selected databases, using wildcards of inclusion and exclusion
• Clone databases
• Copy backup sets to local or UNC folders to create a backup history

Requirements

• xp_cmdshell
  The extended SP xp_cmdshell must be enabled, as the SPs need access to the file system.
• Access Rights
  The SQL Server Agent service account (when using SQL Server full edition) needs modify rights (at least) on the configured folders.

Installation

The SQL Backup Helper can be installed by using the setup binary (Red Gates SQL packager executable). The binary lets you choose the following:

• Database name
• Database folder
• Log folder
• Collation
• Recovery model
• Compatibility level
• Initial database size

The SQL Backup Helper can be installed by manually setting up the database and running the Schema and MasterData scripts.

Examples

-- How to use SQL Backup Helper
-- Example FULL backup command performing a full backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'FULL', NULL, 'master,model,msdb,tempdb'


-- Example FULL backup command performing a FULL backup of all databases 
-- starting with DEV, excluding master,model,msdb,tempdb databases

EXEC USP_BackupDatabase 'FULL', 'DEV%', 'master,model,msdb,tempdb'


-- Example for cleaning up history, keeping 3 backup sets, having no maximum age, 
-- including all databases, excluding master,model,msdb,tempdb databases
EXEC USP_CleanUpHistory 3, NULL, NULL, 'master,model,msdb,tempdb'

-- Example DIFF backup command performing a differential backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'DIFF', NULL, 'master,model,msdb,tempdb'

-- Example TRAN backup command performing a tranaction log backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'TRAN', NULL, 'master,model,msdb,tempdb'

Version History

• 1.0, Initial community release

Links

• Download and follow at Github: https://github.com/Apoc70/SQLBackupHelper

Additional Credits

Additional credits go to Markus Heiliger for the initial version of the SQL Backup Helper.

Follow

• Twitter @stensitzki
• LinkedIn
• Xing