How To Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

How to script all sql jobs

Hello,

Is there a way in sql2k5 to script all sql jobs at once. In sql2k you could right click on the agent and select that option however I do not see it in sql2k5. Thanks.

Hi John. Click the 'Jobs' folder under the SQL Server Agent node in SSMS, then hit the F7 key (brings up the Summary pane). Highlight all the jobs you want to script using a combination of Shift and Ctrl keys, then right click, Script Job as..., then choose where to script to.

HTH,

|||

Chad,

That works. Thanks.

how to run the package trough the stored prcedure

hi focks;

through the jobs is possible it s working fine but

is it possible to run the packages through the stired procedure 2005

ok if possible how please help me

regards

koti

You need to use xp_cmdshell to launch DTEXEC.

How to run multiple jobs in parallel

I have five SQL jobs that I want to run at the same time to save time.
Running them in serialization costs me lot of time so running in parallel is
choice I have. However, they all need to run after 5 DBs are restored. So, I
created one job that does all the restore for these 5 DBs one after another
on nightly basis. So far so good. Now I want to run all these 5 jobs in
parallel so that they all start at the same time to save me lot of time.
Example: I have total of 6 jobs.
Restore All DBs
Step 1: Restore DBCA
Step 2: Restore DBNY
Step 3: Restore DBNJ
Step 4: Restore DBTX
Step 5: Restore DBHI
I have other 5 jobs like this:
1. ProcessDBCA
2. ProcessDBNY
3. ProcessDBNJ
4. ProcessDBTX
5. ProcessDBHI
I need to start running these 5 jobs in parallel as soon as all 5 steps from
job 1 are finished.
Any suggestions.
Thanks in advance.DTS Package can be used to run activities in parallel. So, you can create a
DTS Package, and execute it from the SQL Job.
"Mark" wrote:

> I have five SQL jobs that I want to run at the same time to save time.
> Running them in serialization costs me lot of time so running in parallel
is
> choice I have. However, they all need to run after 5 DBs are restored. So,
I
> created one job that does all the restore for these 5 DBs one after anothe
r
> on nightly basis. So far so good. Now I want to run all these 5 jobs in
> parallel so that they all start at the same time to save me lot of time.
> Example: I have total of 6 jobs.
> Restore All DBs
> Step 1: Restore DBCA
> Step 2: Restore DBNY
> Step 3: Restore DBNJ
> Step 4: Restore DBTX
> Step 5: Restore DBHI
> I have other 5 jobs like this:
> 1. ProcessDBCA
> 2. ProcessDBNY
> 3. ProcessDBNJ
> 4. ProcessDBTX
> 5. ProcessDBHI
> I need to start running these 5 jobs in parallel as soon as all 5 steps fr
om
> job 1 are finished.
> Any suggestions.
> Thanks in advance.|||Thanks for the response.
Any sample code or direction to a whitepaper will be really helpful
"Absar Ahmad" wrote:
> DTS Package can be used to run activities in parallel. So, you can create
a
> DTS Package, and execute it from the SQL Job.
> "Mark" wrote:
>|||There is plenty of information in BOL. Please search for following topics:
DTS, overview
Creating DTS Package Objects and Connections
Creating DTS Package Workflow and Tasks
You can also look for the sub-titles under the following titles in the BOL:
DTS packages
DTS Designer
Best Wishes,
Absar
"Mark" wrote:
> Thanks for the response.
> Any sample code or direction to a whitepaper will be really helpful
> "Absar Ahmad" wrote:
>

How to run multiple dependent SQL Jobs with OSQL

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

DaveCreate a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

-PatP|||I already tried that with no success. The logic looks as follows:

sp_start_job 'DBA Test' -- Inserts one record into a table
go
sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
go
sp_start_job 'DBA Test3' -- Insert a record
go

All jobs execute immediately.

Dave|||That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

-PatP|||Lightbulb.

We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

Dave|||I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

Any suggestions.

Dave

How to Restore the msdb DatBase

Hi,

i have reinstalled the SQL Server before it i have taken backup of both master and
msdb database.Because there were many scduled jobs.
After Installation i want recreate those scduled job.
That i think i can get by restoring msdb.
But it is not restoring how can i restore it.

Or there is any way to get the all scduled job list so that i can recreate it manully.

Regards
Sanjay

Make sure the SQL Server Agent is stopped which uses the msdb database, then the restore should work fine. As a best practise I always get the scripts of the scheduled jobs in addition to the backup of the msdb database. This allows me to execute single script for job creation.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

Hi jens,

i am tryning restore after stopping SQL Server Agent is stopped.

But it is giving the error massage that

"The backup of the system database on device E:\EcoSystemDatabasebackup\msdb_2006070200.BAK can not be restored because it was created by a different version of the server (134218488) than this server (134219767) .RESTORE DATABASE is terminating abnormally.

How can i restore it .while i am able to restore it in another server by different name.

Soisthere is any way to collect information of secduled job form there and create it in re quired server .

it is possible or not ?

if possible how can i do it.

Regards

Sanjay

|||

This isn't replication related, so moving to Engine forum.

By the way, what version of sql server did you take the backup from, and what version are you restoring it to (do select @.@.version)? As the error message says, if you're trying to restore to an earlier version of sql server, it won't work.

|||

Sorry, to come back that late. As greg already said, you have a problem concerning your different versions of your database (might have a service pack or hotfix installed meanwhile ?).

This is what a kn article is telling about the problem: http://support.microsoft.com/kb/264474/EN-US/

Another option would be to transfer the data via DTS, although I never tried that I would make sure to have a backup of the current (running msdb) first.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi guys,

As i saw your goal it is to recreate the same jobs on another MS Sql Server.

This can be done very simple without using the backup/restore mechanism. Just start Enterprise Manager from the server where you have all the jobs.

Go in the tree in Management/SQL Server Agent/Jobs and from the context menu choose generate SQL Script. You will get a nice file with all your jobs.

This script file you will need to run it on the new SQL Server to create all the jobs there.

I hope will help.