Script to Add a New SQL Differential Backup Job With Daily Incremental Zip Archives

This script builds on the differential backup script, adding a zipped incremental backup. This script will create a zip file for each day of the week. I’ve used this script when I have a simple database type but need the flexibility of multiple daily differential backups. You will have to turn on xp_cmdshell to run this script.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM). This script also creates daily incremental zip archives.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [PBSA] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH  DIFFERENTIAL ,  DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'xp_cmdshell ''C:\PROGRA~1\WinZip\winzip32.exe -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.bak - %date:~0,3%.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"''', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=193000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO