A few days back, my friend Pankaj was enquiring about a particular case where he needed to automate the backup (in the form of SQL scripts) of all SQL Jobs (schedules) on a SQL Server. DTS doesn't support automatic scripting of SQL Jobs from what I could tell. I remembered doing something similar a few months back on a particular project but I didn't have the T-SQL snippet for it, so I looked up a bit and found the original solution at the Microsoft website, which shouldn't take too long to load if you're running broadband. The solution describes how to use Transact-SQL and SQL Distributed Management Objects (SQL-DMO) to script out jobs to a file, in Transact-SQL format. This seems to have helped Pankaj and he has written this nice stored procedure for it all. Thanks for sharing dude! [sql] CREATE PROCEDURE ScriptAllJobs @DirectoryName varchar(200) AS --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(240) -- 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 = @DirectoryName + '' + cast(day(getdate()) as varchar(2)) + datename(month, getdate()) + cast(year(getdate()) as varchar(4)) + '-jobs.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 [/sql] Pankaj also tells me ...

You cannot take a [regular] backup of the sql job as we do for a DB. But we can script the jobs so that in case of a server failure the jobs can be re-created with scheduling options. ... You just need to pass in the directory [to the SP] where the job script is to be stored. This script will only take backup of jobs of the server on which it is run (ie. the local server). If you want to use this on a remote server than need to change the script to accept the server name and login info.