Applying jobs in Sql Server 2000

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Applying jobs in Sql Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-09 : 08:17:55

Nagarjuna writes "Hai
i have a Query like below mentioned to take back up my DataBase. how i can do by using this query, to create a jobs in my dataBase at pertucular time(i.e., 12 hours per a day).

how i can configure by using this peace of code, explain me step by step

BACKUP DATABASE MSPetShop to DISK = 'D:\MSPetShop.bak'

this is job execution plan

EXEC sp_add_jobschedule @job_name = 'NightlyBackup',
@name = 'ScheduledBackup',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_time = 10000"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 10:42:13

You need a job with a job step before you try to create the schedule

--Create the job
EXEC sp_add_job @job_name = 'NightlyBackup'

--Create the job step
EXEC sp_add_jobstep @job_name = 'NightlyBackup',
@step_name = 'Make Backup',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE MSPetShop to DISK = ''D:\MSPetShop.bak''',
@retry_attempts = 5,
@retry_interval = 5

--Now schedule job
EXEC sp_add_jobschedule @job_name = 'NightlyBackup',
@name = 'ScheduledBackup',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_time = 10000

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-11-09 : 10:43:57

Nagarjuna,

The script below probably has more than you need but it should work for you. I created a job in Enterprise Manager(Enterprise Manager>Management>SQL Server Agent>right click to create new job) and then scripted it out once it is complete. I used the same job name as what you posted so be careful if you run this script below because it will drop the existing job if it exists.

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'NightlyBackup')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''NightlyBackup'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'NightlyBackup'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'NightlyBackup', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'BackupDatabase', @command = N'BACKUP DATABASE MSPetShop to DISK = ''D:\MSPetShop.bak''', @database_name = N'MSPetShop', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'ScheduledBackup', @enabled = 1, @freq_type = 4, @active_start_date = 20061109, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 12, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 10:58:23

Actually the point is - use Enterprise Manager to create your jobs, in fact Books Online even says "SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources