Script to Add a New SQL Differential Backup Job

I know you’ve been waiting for this… This script will add a differential backup job to Microsoft SQL Server 2005. Use this script in conjunction with a full backup.

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

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 [DBName] 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'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.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=170000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO