Script all jobs on SQL 2005/2008 Server

Question

Script all jobs on SQL 2005/2008 Server

Roust_m

SSCoach

Points: 17410
More actions
February 1, 2009 at 6:34 pm

#130649

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.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

yzhu117 SSC Enthusiast Points: 198 More actions · Answer 1

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.

Roust_m SSCoach Points: 17410 More actions · Answer 2

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.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

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()

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.

Roust_m SSCoach Points: 17410 More actions · Answer 4

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.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 6

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

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Rudy Panigas (3/11/2009)
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

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

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

Rudy Panigas

SSChampion

Points: 10702

March 11, 2009 at 7:56 am

#957486

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

Rudy

Simon D Richards Ten Centuries Points: 1048 More actions · Answer 9

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

Set fso = CreateObject("Scripting.FileSystemObject")

Set iFile = fso.CreateTextFile(strFilename, True)

iFile.Write (strJob)

iFile.Close

Set fso = Nothing

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 10

I use VBS for 2000 also but you need to use SMO on 2005/8 and that is what is in the post. One of the Dot Net languages or Powershell can use SMO.

The other option is to try to find something that someone already wrote is willing to share, or purchase something.

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.

marat-oz Old Hand Points: 322 More actions · Answer 11

Thanks for the script.

It worked for me on SQL Server 2005.

I created a job with this ActiveX script and it was just perfect.

Maybe I will think about changing the script to script jobs into separate files as an option.

genehunter29009 SSC Eights! Points: 877 More actions · Answer 12

GO

/****** Object: StoredProcedure [dbo].[DBA_DR_Generate_Script_SQL_Agent_Jobs] Script Date: 12/14/2011 13:34:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*Requires this run to turn on feature

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

*/

CREATE PROCEDURE [dbo].[DR_Generate_Script_SQL_Agent_Jobs]

-- Add the parameters for the stored procedure here

@FileName varchar(200), -- File name to script jobs out

@ps1filename Varchar(300) -- File name to create powershell script

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

--sp_OA params

DECLARE @cmd varchar(255) -- Command to run

DECLARE @oSQLServer int -- OA return object

DECLARE @hr int -- Return code

-- version parameters

DECLARE @sqlver sql_variant

DECLARE @sqlver2 varchar(20)

DECLARE @sqlver3 int

--User params

DECLARE @server varchar(30) -- Server name to run script on. By default, local server.

Select @server = @@SERVERNAME

--SQL DMO Constants

DECLARE @ScriptType varchar(50)

DECLARE @Script2Type varchar(50)

SET @ScriptType = '327' -- Send output to file, Transact-SQL, script permissions, test for existence, used quoted characters.

SET @Script2Type = '3074' -- Script Jobs, Alerts, and use CodePage 1252.

--Set the following properties for your server

-- Get Verion and GOTO it

SELECT @sqlver = SERVERPROPERTY('productversion')

SELECT @sqlver2 = CAST(@sqlver AS varchar(20))

select @sqlver3 = SUBSTRING(@sqlver2,1,1)

-- 1 = 2008 8 = 2000 and 9 = 2005 1 is short for 10

BEGIN

--select @sqlver3 only uncomment to see state of version

IF @sqlver3 = 1 GOTO SERVER2008

IF @sqlver3 = 9 GOTO SERVER2000

IF @sqlver3 = 8 GOTO SERVER2000

GOTO THEEND

END

SERVER2000:

--insert into file to create categories. will error out for categories that exist but works, bug in 2000

DECLARE @cmd5 sysname, @var sysname

-- truncate file or create file

SET @var = 'echo off > '

SET @cmd5 = @var + @filename

EXEC master..xp_cmdshell @cmd

select @cmd5

-- Append to the file after Truncating

--cursor to Insert rows into @filename

DECLARE @stringname VARCHAR(1000)

DECLARE db_cursor CURSOR FOR

select (

'EXEC msdb.dbo.sp_add_category @name = ' + char (39) + name + char (39) )

from msdb.dbo.syscategories (nolock)

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @stringname

WHILE @@FETCH_STATUS = 0

BEGIN

-- insert stringname into @filename

DECLARE @cmd6 VARCHAR(4000)

SET @cmd6 = 'echo' + ' ' + @stringname + ' >> ' + @filename

EXEC master..xp_cmdshell @cmd6

-- end insert stringname into @filename

FETCH NEXT FROM db_cursor INTO @stringname

END

CLOSE db_cursor

DEALLOCATE db_cursor

-- enter one more GO before the job script runs.

SET @cmd6 = 'echo GO >> ' + @filename

EXEC master..xp_cmdshell @cmd6

-- now append to the same file job scripts

--CREATE The SQLDMO Object

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT

--Set Windows Authentication

EXEC @hr = sp_OASetProperty @oSQLServer, 'LoginSecure', TRUE

--Connect to the Server

EXEC @hr = sp_OAMethod @oSQLServer,'Connect',NULL,@server

--Script the job out to a text file

SET @cmd = 'Jobserver.Jobs.Script(' + @ScriptType + ',"' + @FileName +'",' + @Script2Type + ')'

EXEC @hr = sp_OAMethod @oSQLServer, @cmd

--Close the connection to SQL Server

--If object is not disconnected, the processes will be orphaned.

EXEC @hr = sp_OAMethod @oSQLServer, 'Disconnect'

--Destroy object created.

exec sp_OADestroy @oSQLServer

GOTO THEEND

SERVER2008:

declare @sql3 varchar(200)

declare @sql varchar(900)

declare @sql2 varchar(900)

-- set execution policy to allow scripts to be run.

set @sql ='Set-ExecutionPolicy RemoteSigned'

set @sql2 = 'powershell -c "' + @sql + '"'

exec xp_cmdshell @sql2

--start building ps1 file to execute

set @sql ='[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") '

set @sql2 = ' echo ' + @sql + ' > ' + + @ps1filename

exec xp_cmdshell @sql2

set @sql ='$server = New-Object Microsoft.SqlServer.Management.Smo.Server("' + @server + '")'