Ashutosh Nilkanth’s Blog

SQL Jobs Auto-backup

 Loading ...

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

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

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.