Script all SQL Server Job automatically by SSIS and SMO
As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.
We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.
Let us see, how it goes!!!
Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.
Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:
Here is the code I have written in “Main()” method of script editor.
// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);
string script = "";
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);
//concate the text of job
foreach (string s in strCol)
script += s;
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(‘:’, ‘_’).ToString() + ".sql");
Dts.TaskResult = (int)ScriptResults.Success;
Dts.TaskResult = (int)ScriptResults.Failure;
Here is the screen capture of my script editor.
Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.