Back SQL Agent Jobs

  • 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 🙂

  • 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

    [font="Courier New"]' 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"[/font]

  • 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

  • Thanks Philip, yes i am a bit late to join the community,i will try it on monday, will be here more often

    Cheers

  • hi Greg Charles ,

    thx for ur information. it helps me too.

    thx

    sreejith

    MCAD

  • 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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    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

  • backing up msdb database will backup your agent jobs, its also a good idea to script them out.

    Gethyn Elliswww.gethynellis.com

  • Hi Philip

    Thanks a lot mate, it worked for me,

    Cheers

  • 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

  • You may find http://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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    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

  • Thanks Philip,

    Exactly what I was looking for!

    Regards,

    Hanslindgren

  • Dear CrazyMan and All,

    Could you please answer few queries reg sp_OAMethod.

    1. What are all the parameters this method accepts? i mean besides scripting out jobs,Views and SProcs what is it capable of doing.

    2. Is there any way through which i can learn about the operations that this OAMethod can do by myself.

    3. You have mentioned few codes for scripting options like

    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.

    How do i learn about other values, if available.

    4. I would also like to script out the views, then what will be the statement in place of

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

    5. I would like to see the output of the scripting in the Result window instead of a file...then what could be the value in place of @ScriptType.

    6. On checking up, it was found that these OA procedures use "odsole70.dll". But I didn't find this DLL in my machine. I want this because, I can add this DLL to one of my Visual Studio Projects as a reference and study the avaiable properties and methods, if possible. Is there any way of acheiving this?

    Living Dead,

    [font="Comic Sans MS"]Vampire[/font]

    --In 'thoughts'...
    Lonely Rogue

  • 1. From the View menu, select "Object Explorer Details"

    2. Select "Sql Agent jobs - Jobs" in the "Object Explorer" pane.

    3. Highlight all of the jobs in the "Object Explorer Details" pane.

    4. Right click the highlighted jobs and script them.

    This allows all jobs to be scripted at once.

  • Thank you very much Richard. Your solution is very elegant and I can use it on production servers, where installing extra components like ServerFineBuild is not an option.

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

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