SSIS Package to Backup Jobs Script using SMO.

This package willl use some SMO reference and generates SQL Server Jobs, Operators and Alerts script backup.

I had written the code for SQL Server Jobs only and commented code for Operators backup and Alerts Backup.

You just need to replcace Operators or Alerts instead of Jobs.

I have created SSIS package that automatically creates backup folder for current date and save your all jobs with jobsname.sql separately as well as all jobs in one file with static name AllJobsInOneFile.sql.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0″ + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0″ + (DT_STR,2,1252)DAY(GETDATE()),2)

Step3 : Now Right Click on Script task and select all user variables to ReadOnlyvariables.

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.ScriptTask.

Paste below code to your script task code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
#endregion

namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            StringCollection sc = new StringCollection();
            ScriptingOptions so = new ScriptingOptions();
            so.IncludeDatabaseContext = true;

            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" + FolderDate.ToString());
            try
            {
                string script = "";

                string JobName;
                string AllJobsInOneFile = "";
                //Loop over all the jobs
                //To take a script backup of Operators.

                //foreach (Job J in srv.JobServer.Operators)
                //To take a script backup of Alerts.
                //foreach (Job J in srv.JobServer.Alerts)
                //To take a script backup of Jobs.
                foreach (Job J in srv.JobServer.Jobs)
                {

                    //Output name in the console
                    Console.WriteLine(J.Name.ToString());

                    JobName = J.Name.ToString();
                    sc = J.Script(so);

                    //Get all the text for the job
                    foreach (string s in sc)
                    {
                        script += s;
                    }

                    //Generate the file
                    TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + JobName.Replace(':', '_').ToString() + ".sql");
                    tw.Write(script);
                    tw.Close();
                    TextWriter WriteAllJobs = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + "AllJobsInOneFile.sql", true);
                    WriteAllJobs.Write(script + "\nGO\n");
                    WriteAllJobs.Close();
                    script = "";
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar