backup job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

backup job

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-15 : 15:19:26

One of our backup JOB is enabled. Cheked job properties

Scheduled to run every day
last run : 09/24/2009-succeded
next run : 11/19/2009

Why the next run is on 11/19 since job is scheduled to run daily. Please advice.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-15 : 15:24:34

What is the Start Date of the schedule?

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-15 : 15:26:18

10/11/2007

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-15 : 15:30:11

Can you post the sql script of the schedule.

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-15 : 15:55:59

USE [msdb]
GO
/****** Object: Job [Backup master (Full)] Script Date: 10/15/2009 15:52:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/15/2009 15:52:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Backup master (Full)',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'xxxxx\xxxxx',
@notify_email_operator_name=N'DBMail', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup database] Script Date: 10/15/2009 15:52:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @path VARCHAR(256)
SET @path = ''h:\backups\master'' + ''-'' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 126), '':'', ''-''), ''.'', ''-'') + ''.bak''

BACKUP DATABASE master TO DISK = @path
WITH NAME=''master-Full Database Backup'', FORMAT, INIT, CHECKSUM',
@database_name=N'master',
@output_file_name=N'h:\sqljoboutput\master\full\joboutput.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20071127,
@active_end_date=99991231,
@active_start_time=2500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-15 : 16:25:21

The script looks fine to me. I compared it to our daily full backup job and they are practically identical for the job schedule. Have you tried restarting the agent service? What's @@VERSION show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-15 : 17:20:04

quote:

@active_start_time=2500,

This part of the script doesn't look right to me. What hour is 2500 meant to be?

Here's what BOL says:
[ @active_start_time = ] active_start_time
Is the time on any day between active_start_date and active_end_date to begin execution of the job. active_start_time is int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-15 : 17:25:45

Resolved the issue. Actually this is dev server and I am newly joined inthis project. In the morning I noticed that SQL server agent was not running and restarted, so that's the reason it is showing the next run as 10/16. Earlier they were not taking the backups.Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources