SQL Jobs Auto-backup

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!

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.

8 thoughts on “SQL Jobs Auto-backup”

  1. Hi Ashutosh,

    The script seems very helpful but it runs successfully but does not output the file to the directory provided. Can you please shed some light? It says, “Command(s) completed successfully.” but I do not see any file in the directory I provided. For instance, EXEC ScriptAllJobs ‘C:\OUTPUTFOLDER\’

    Thanks,
    daniel.

  2. This doesn’t seem to work for SQL2008. Sproc runs and is successful, but no script is created…I really need this do you know what this might be?

  3. Just a note. OLE Automation Procedures on the server should be enabled in order to run the procedure. By default this option is disabled. You may enable it by…

    USE master
    GO

    sp_configure ‘show advanced options’, 1;
    GO

    RECONFIGURE
    GO

    sp_configure ‘Ole Automation Procedures’, 1
    GO

    RECONFIGURE;
    GO

Leave a Reply

Your email address will not be published. Required fields are marked *