Change BackupTime.sql

--This script is designed to reschedule 'day backups' to 'evening backups'
--it assumes that you want to re-run the backup tonight and at the new time
--into the future.  It intentionally re-runs the backup 'today' so you do not
--go > 24 hours without a backup.
--
--Change your needed lines 32-35 below
--
--
-- make sure we are using ksubscribers
use ksubscribers
-- declare variables
Declare @FullID int
Declare @IncID int
Declare @Agent numeric(26)
Declare @NewFullTime datetime
Declare @NewIncTime datetime
Declare @CurrentFullTime datetime
Declare @CurrentIncTime datetime
Declare @CurrFullYear int
Declare @CurrFullMonth int
Declare @CurrFullDay int
Declare @CurrIncYear int
Declare @CurrIncMonth int
Declare @CurrIncDay int
Declare @NewHour int
Declare @NewMin int
Declare @GoLive int
--set variables
set @FullID = 176
set @IncId = 177
--change Agent Guid variable here
set @Agent = '199710340135637'--change this value and the next two
set @NewHour = 17 -- integer hour in 0-23 format
set @NewMin = 30 -- integar minute in 0-59 format
set @GoLive = 0 -- change to 1 to enable the actual change
--
--
--get and calculate time for new backups -- DO NOT CHANGE ANYTHING BELOW THIS LINE
--
--
SELECT @CurrentFullTime = execScriptTime FROM [ksubscribers].[dbo].[scriptAssignment] where scriptid=@FullID and agentGuid =@Agent
SELECT @CurrentIncTime = execScriptTime FROM [ksubscribers].[dbo].[scriptAssignment] where scriptid=@IncID and agentGuid =@Agent
set @CurrFullYear = DATEPART(yyyy,@CurrentFullTime)
set @CurrFullMonth = DATEPART(mm,@CurrentFullTime)
set @CurrFullDay = DATEPART(dd,@CurrentFullTime)
set @CurrIncYear = DATEPART(yyyy,@CurrentIncTime)
set @CurrIncMonth = DATEPART(mm,@CurrentIncTime)
set @CurrIncDay = DATEPART(dd,@CurrentIncTime)
set @newfulltime = dateadd(ss,0,dateadd(mi,@newmin,dateadd(hh,@newhour,DateAdd(yy, @CurrFullYear-1900, DateAdd(m,  @CurrFullMonth - 1, @CurrFullDay - 1)))))
set @NewIncTime = dateadd(ss,10,dateadd(mi,@newmin,dateadd(hh,@newhour,DateAdd(yy, @CurrIncYear-1900, DateAdd(m,  @CurrIncMonth - 1, @CurrIncDay - 1)))))

--List current backup Scedule(s) - scriptID 176 = full, 177 = imcremental (fyi)
--select @NewFullTime
--select @NewIncTime
SELECT scriptId,execScriptTime as OrigExecScriptTime FROM [ksubscribers].[dbo].[scriptAssignment] where scriptid in (@FullID,@IncID) and agentGuid =@Agent

-- *********  uncomment below 2 lines to actually make a change ********
if @GoLive = 1
	begin
		update dbo.scriptAssignment set execScriptTime=@NewFullTime where scriptid =@FullID and agentGuid =@Agent
		update dbo.scriptAssignment set execScriptTime=@NewIncTime where scriptid =@IncID and agentGuid =@Agent
	end
	
--List new backup Scedule(s) - scriptID 176 = full, 177 = imcremental (fyi)
SELECT scriptId,execScriptTime as NewExecScriptTime FROM [ksubscribers].[dbo].[scriptAssignment] where scriptid in (@FullID,@IncID) and agentGuid =@Agent