Script all jobs on SQL 2005/2008 Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Script all jobs on SQL 2005/2008 Server

15 posts, Page 1 of 2

1 2 »»

Script all jobs on SQL 2005/2008 Server

Rate Topic

Display Mode

Topic Options

Author

Message

Roust_m

Posted Sunday, February 01, 2009 6:34 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:36 PM
Points: 961, Visits: 1,532

Hi,

I need to automate scripting of all jobs on a SQL 2005/2008 server. I can't use any Integration Services packages, and apparently SCPTXFR.EXE is no longer present in SQL Server 2005.

Just wondering if there is an existing script for this task, so I could put it as a job step?

Thanks.

Post #647768

yzhu117

Posted Monday, February 02, 2009 7:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 12:36 PM
Points: 78, Visits: 82

In sql server 2005, you can use sql management studio --> select the Job folder in object explore --> select all jobs in object explore details window--> right click and script all the jobs. Some modifications may need if you want to deploy in another computer. Hope it helps.

Post #648030

Roust_m

Posted Monday, February 02, 2009 3:00 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:36 PM
Points: 961, Visits: 1,532

No, this is not what I want. I don't want a manual process, I want automatic. E.g. a job running every night and scripting all the jobs into a text file.

Post #648419

MG-148046

Posted Tuesday, February 03, 2009 6:10 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,625, Visits: 1,926

you can use one of the dot net languages with SMO to script the jobs. This is a snippet from something I did in VB.Net. Please keep in mind that I am not very good with VB. Of course you will need to create all your variable, objects, streamwriter, etc.

There are quite a lot of examples on the net. It's the only way I could do it since I'm not a dot net person.

wf.WriteLine()
wf.WriteLine("-- **** Create Jobs")
wf.WriteLine()

Dim jb As Agent.Job
For Each jb In svr.JobServer.Jobs
smoObjects = New Urn(0) {}
smoObjects(0) = jb.Urn
scrp.Options.IncludeIfNotExists = True
Dim sc As StringCollection
sc = scrp.Script(smoObjects)
Dim st As String
For Each st In sc
wf.WriteLine(st)
wf.Flush()
Next
Next

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #648762

Roust_m

Posted Tuesday, February 03, 2009 4:00 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:36 PM
Points: 961, Visits: 1,532

I am not good with VB either. In fact I have no knowledge of it at all, because I am not a developer. I was just wondering if there is a TSQL script already developed, so I don't have to develop it myself.

Post #649424

MG-148046

Posted Wednesday, February 04, 2009 5:42 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,625, Visits: 1,926

The snippet is part of a much larger program that scripts out every object in a database so it would be hard to break down.

Maybe someone who is good with Powershell can help. From some of the PS code I've seen, using the PS SQL provider, someone can probably do this as a PS script.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #649707

Rudy Panigas

Posted Wednesday, March 11, 2009 7:20 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 306, Visits: 1,016

Hello everyone,

I believe this will make everyone happy and there is not coding involved. Just search for SQLScripter. A gentleman has create a neat application to script out everything or just some things including jobs. Just modify the confiig file and execute.

That's it.

Rudy

Post #673226

george sibbald

Posted Wednesday, March 11, 2009 7:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 5,270, Visits: 11,212

Rudy Panigas (3/11/2009)

can you provide a URL, search on sqlscripter produced nothing obvious

---------------------------------------------------------------------

Post #673242

Rudy Panigas

Posted Wednesday, March 11, 2009 7:56 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 306, Visits: 1,016

I just Googled it. http://www.sqlscripter.com/

Post #673262

Simon D Richards

Posted Wednesday, March 11, 2009 8:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:18 PM
Points: 141, Visits: 501

Hi, I use the following as an active x script from a job on SQL 2000:

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "##Server_Name##"

strFilename = "C:\JOBS.sql"

For Each oJB In conServer.JobServer.Jobs
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing

Post #673299

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions