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.