Programs

SQL_STORED_PROCEDURE - prc_create_backup_jobs

Main program

Name	prc_create_backup_jobs
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Create jobs for backup- and check_backup procedures. PARAMETERS - 1 @backup_path VARCHAR(255) Path to save backupfiles to. - 2 @subdir BIT Add database name as subdirectory to backup path, 0 / 1 (default). - 3 @verify BIT Verify backups, 0 (default) / 1. - 4 @split BIT Split userdb full backupfiles when large database, 0 (default) / 1. - 5 @keep_days INT Delete backupfiles older than keep days, 90 (default) / 0 / n. Value 0 means no cleanup.r - 6 @force_full_backup BIT Force a full backup during diff or log backup when there is no full backup found in the msdb history, 0 (default) / 1. - 7 @full_backup_weekday VARCHAR(3) Job schedule weekday for the full backup job, 'SAT','SUN', 'MON', 'TUE', 'WED', 'THU' or 'FRI'. - 8 @full_backup_start_hour INT Job schedule start hour for the full- and diff backup jobs (0-23). - 9 @log_backup_interval_minutes INT Job schedule interval in minutes for the user databases transaction log backup, 15 / 30 / 60 (default). - 10 @system_backup_interval_hours INT Job schedule interval in hours for the system databases backup, 1 / 2 / 4 (default) / 6 / 8 / 12 / 24. - 11 @error_report_path VARCHAR(255) Path to deliver the error report file. - 12 @operator_name NVARCHAR(128) Operator name to notify failed jobs. - 13 @email_address NVARCHAR(100) Operator email address. Also used to deliver error report files. - 14 @option VARCHAR(6) 'CREATE' (default) (re)create backup jobs / 'LIST' List backup jobs Job Category 'Database Maintenance' is created if not exists. Job Operator with name @operator_name and email_address @email_address is created if not exists. Jobs are (re)created: - Name: {dbname} - Backup Systemdb Schedule: Daily every @system_backup_interval_hours hours between 00:55:00 and 23:59:59 - Name: {dbname} - Backup Userdb Full Schedule: Weekly on @full_backup_weekday at @full_backup_start_hour hh:05:00 - Name: {dbname} - Backup Userdb Diff Schedule: Weekly on every other weekday than @full_backup_weekday at @full_backup_start_hour hh:05:00 - Name: {dbname} - Backup Userdb Log Schedule: Daily every @log_backup_interval_minutes minutes between 00:00:00 and 23:59:59 - Name: {dbname} - Check database backup Schedule: Daily every 1 hour between 00:50:00 and 23:59:59 EXAMPLES -- Create backup jobs using all defaults. EXEC [dbo].[prc_create_backup_jobs] @backup_path='C:\Data\MSSQL\backup', @error_report_path='C:\Data\MSSQL\error_report', @operator_name='SQL Beheerder', @email_address='g.mantel@gmail.com'; -- List backup jobs EXEC [dbo].[prc_create_backup_jobs] @option = 'LIST'; USED OBJECTS - dbo.fun_jobschedule2description (udf in local database) - dbo.fun_trim (udf in local database) - dbo.fun_jobtime2time (udf in local database) HISTORY 2026-05-12 - Created procedure 2026-04-13 - Implemented @force_full_backup for procedure prc_backup_userdb_diff - Changed parameter @force_full_backup default to 1 - Minimum value for @log_backup_interval_minutes is now 15 minutes. TAGS <program> <description>Create jobs for backup- and check backup procedures</description> <generic>0</generic> <author>Gerrit Mantel</author> <created>2026-05-12</created> <lastmodified>2026-05-13</lastmodified> </program>
Description	Create jobs for backup- and check backup procedures
Minversion
Generic	0
Author	Gerrit Mantel
Created	2026-05-12 00:00:00
Lastmodified	2026-05-13 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	3
Sourcecode

Sub programs