Solved

how to create job from .sql script?

Posted on 2008-06-19
3
4,095 Views
Last Modified: 2010-04-21
I have a sql job (which uses Idera SQLSafe to backup our databases) with 60 steps.  Each step is a CmdExec command like the following example:

"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "Audit" "\\SQLSafe_server1\h$\SQLSafe\Wednesday\dbserver_mydatabase_01.safe" -server "dbserver" -backupdescription "mydatabase" -backupname "mydatabase" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt

I need to change the 'Wedensday' to 'Thursday' (and other days as well) and though I can go into each step and do it one by one, this is entirely too time consuming.

I generated a sql-script from the job in question, of which I attached as a code snippet.

The question is this.... How do I create a job from that sql-script?

If I create a new job and try to 'Open' that as a command entered as a step within that job, I get an error advising me that there are 50220 characters which is too large.  Also, even I could do it this way, I wouldn't be creating the job the way I wanted to originally, which is a job with many CmdExec steps (and which is the way the job is originally created).

Thanks
-- Script generated on 6/19/2008 9:44 AM

-- By: sa

-- Server: dbserver
 

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'SQLSafe - All Selected DBs - Wednesday')       

  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 ''SQLSafe - All Selected DBs - Wednesday'' 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'SQLSafe - All Selected DBs - Wednesday' 

    SELECT @JobID = NULL

  END 
 

BEGIN 
 

  -- Add the job

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'SQLSafe - All Selected DBs - Wednesday', @owner_login_name = N'sa', @description = N'SQLsafe Backup', @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'SQLsafeCmd: Backup mydatabase', @command = N'"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "mydatabase" "\\SQLSafe_server\h$\SQLSafe\Wednesday\dbserver_mydatabase_01.safe" -server "dbserver" -backupdescription "mydatabase" -backupname "mydatabase" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3

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

  -- another 58 steps run between the above and the below steps (EDITED for the sake of this example) --

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 60, @step_name = N'SQLsafeCmd: Backup mydatabase2', @command = N'"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "mydatabase2" "\\SQLSafe_server\h$\SQLSafe\Wednesday\dbserver_mydatabase2.safe" -server "dbserver" -backupdescription "mydatabase2" -backupname "mydatabase2" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt ', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @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 = 1

  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'Weekly Wednesday', @enabled = 1, @freq_type = 8, @active_start_date = 20070102, @active_start_time = 223000, @freq_interval = 8, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @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:

Open in new window

0
Comment
Question by:jam21000
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
Your script creates the job itself, so run it in a new query editor window.
0
 
LVL 1

Author Closing Comment

by:jam21000
Comment Utility
Ha!  I thought so and looking at the comments the sql-script wrote (which I looked at/realized just now, stupid me), I should have seen the confirmation there already.

I just modified the script and ran it in query analyzer and it created the job.

Thanks for the quick response/confirmation.  much appreciated.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
You're welcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now