• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 35
  • Last Modified:

how can I export all sql agent jobs to a single .sql or .txt file every evening?

I am running SQL Server 2012 and I have a need to export all jobs on a SQL instance to a single .sql file every evening. Is this possible? I have found a way to select them all via the GUI and selecting "script job as", however I need this automated. Also the secondary SQL server instance is offline all the time, so the SSIS task of transfer jobs does not appear to work. I found this script, but I am not able to figure out how to get it to export the jobs to a .txt or .sql file.
-- not sure where I tell this code to export the file to...
DECLARE @object int
exec sp_OACreate 'SQLDMO.SQLServer', @object OUT
exec sp_OASetProperty @object, 'LoginSecure', TRUE
exec sp_OAMethod @object,'Connect(my_sql_server)'
exec sp_OAMethod @object, 'Jobserver.Jobs().Script()'
exec sp_OADestroy @object

My goal is to have all jobs dumped to \\file_share\sql_instance\jobs.sql file every evening.

Thanks experts!
  • 4
1 Solution
PadawanDBAOperational DBACommented:
This should do the trick for you: In SSMS, expand SQL Server Agent > Highlight JobsView menu > Object Explorer Details > Multi-select all the jobs you want > Right click > Script Job As > Create To > New Query Window

Edit: be aware that the job owner needs to exist wherever you are moving them to. ( search for @owner_login_name in the sql query window to cycle through all of them )
sqlagent007Author Commented:
How would I schedule the steps above as an automated job every evening?

I need the jobs scripted out every day, as sys admins and other folks add jobs, and I need to know the most current file has all the jobs as of the last 24 hours.
sqlagent007Author Commented:
This seems to do what I want...I just don't know how to execute this script, or how to force the output to a UNC path. I would also need to schedule this every evening.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dsackerContract ERP Admin/ConsultantCommented:
You might find POWERSHELL to your liking, which you can put on your database server into the Task Scheduler, and run it nightly. This is an example of the few lines of code needed to script out all your jobs nightly:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$OutputFile = "C:\Jobs\SytelineDev.sql"
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "DATABASE\INSTANCE"
$srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file $OutputFile

Open in new window

I just saw that you referenced something similar. The way you will do this is:

1. Save the code above into a file (on the server), called ScriptAllJobs.ps1
2. Start your Task Scheduler.
3. Create a new job.
4. In the Action, you will add the following command:
        *  powershell -ExecutionPolicy ByPass -File ScriptAllJobs.ps1
        * (Make sure you reference the folder where you put the file.)
5. Set your schedule for nightly.

It will be good to test this out, as you'll need to change the DATABASE\INSTANCE, the output file, etc. If you don't use an INSTANCE, just take off the \INSTANCE part.
sqlagent007Author Commented:
I like yours way better!!! I am working on scheduling it via a SQL agent job as this is better for what we are doing. The windows task will get overlooked because we never use them. Somebody will forget to update it.
sqlagent007Author Commented:
Thanks! Love this, simple and easy
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now