SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Back SQL Agent Jobs


Back SQL Agent Jobs

Author
Message
CrazyMan
CrazyMan
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3062 Visits: 1597
Hi All
I need to back up all the Agent Jobs i have on my servers, is there some ways to automate this process, rather than go into the Enterprise manager, script the job and then save it

Please post your suggestions Smile
Philip Kelley
Philip Kelley
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1585 Visits: 232
A long time ago--ok, file timestamp says 5 months, but I don't remember doing any of it--I created a simple VB script utility to script out all the jobs from a given SQL instance. (This was for 2005; I had tried something similar for 2000 years ago, but various features I came across meant it didn't work too well.) This was a manual process, as you can see from the use of MsgBox.

This uses SQLDMO, which I'm 99% certain is what SSMS uses to generate scripts. It's all documented in BOL and online, and at this time I couldn't tell you what it all means. Start with this, do some research on what it all means and how it might be adapted to your system, and (I'm hoping) you should be able to work it all out.

Good luck!

Philip


' This will generate build scripts for all SQL Agent jobs on the target server (xxx)
' and save them to file "AllJobs.sql".
'
' First # parameter:
' 4 - Create the object
' 64 - Output to file (only)
' 4096 - Create only if it doesn't exist
' 131072 - Include header and descriptive info
'
' Second # parameter:
' 2048 - SQL Agent [as opposed to something else]
'


'Set the server to work over
targetServer = "xxx"


'Let them know it started and where it's pointing; user selects Ok (1) or Cancel (2)
If MsgBox("Work over jobs on " & targetServer & "?", 257, "Jobs Processing") = 2 Then
'Exit the script
Wscript.echo "Cancelled"
Wscript.quit
End If


' Configure object
DIM loDMO, loSQLInstance, loJobs
SET loDMO = CreateObject("SQLDMO.SQLServer2")


' Login to server with NT authentication
loDMO.LoginSecure = TRUE
loDMO.Connect(targetServer)


' Generate the scripts
lsScripts = loDMO.JobServer.Jobs.Script(135236, "JobsFrom_" & targetServer & "__All.sql", 2048)


Wscript.echo "Completed"




Greg Charles
Greg Charles
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11637 Visits: 5985
Suji,

Since you mention Enterprise Manager, I assume you're talking SQL 2000. You know you can script all the jobs in an instance in Enterprise Manager by right_clicking on 'Jobs' under 'Management'. You don't have to script each one individually.

Greg

Greg
CrazyMan
CrazyMan
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3062 Visits: 1597
Thanks Philip, yes i am a bit late to join the community,i will try it on monday, will be here more often


Cheers
Sreejithsql
Sreejithsql
SSC Eights!
SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)

Group: General Forum Members
Points: 999 Visits: 134
hi Greg Charles ,
thx for ur information. it helps me too.

thx
sreejith
MCAD
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14275 Visits: 3903
All your jobs are stored in msdb, so a backup of msdb will contain all your jobs.

As other people have said, it is a good idea to also have scripts for all your jobs. This means they can be re-created if you need to build a new server, or for any other reason.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2809 Visits: 2905
backing up msdb database will backup your agent jobs, its also a good idea to script them out.

Gethyn Ellisgethynellis.com
CrazyMan
CrazyMan
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3062 Visits: 1597
Hi Philip
Thanks a lot mate, it worked for me,


Cheers
CrazyMan
CrazyMan
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3062 Visits: 1597
Hi All
I have also found a way of scripting using SQL DMO , i have used this code on SQL 2005 and its working, you have to enable OLE-Automation before you run the code below


--sp_OA params
DECLARE @cmd varchar(255) -- Command to run
DECLARE @oSQLServer int -- OA return object
DECLARE @hr int -- Return code

--User params
DECLARE @FileName varchar(200) -- File name to script jobs out
DECLARE @Server varchar(30) -- Server name to run script on. By default, local server.

--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
SET @FileName = 'c:\sqlJobs.sql'
SET @Server = @@SERVERNAME

--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
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14275 Visits: 3903
You may find www.codeplex.com/SQLServerFineBuild helpful. FineBuild is something I published that helps you get a 1-click install and configuration of SQL Server 2005. It includes scripts that help in upgrading from SQL 2000, including scripting out jobs, accounts, etc, etc.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search