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!
LVL 1
sqlagent007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 )
0
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.
0
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.

http://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
sqlagent007Author Commented:
Thanks! Love this, simple and easy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.