Using SMO to script all SQL jobs for an instance using C#.Net

Introduction

This code serves to show how SMO can be used to script a collection of objects, in this case, SQL Jobs, in their entirety to a text file for source control or transfer to a different server/instance.

Background

This came about when I was looking for a way to capture SQL jobs with all their associated steps, schedules and other configuration information into our Git source repository, it was also used to transfer the job scripts to an AWS environment from a locally hosted on-site server.

Using the code

Upon compiling the code in your environment, you can execute it in a windows command prompt window. When you execute it with no parameters supplied, you will see a screen like this.

The code

<code>using System;
using System.Globalization;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;

namespace SQLJobScripter
{
    static class SqlJobScripter
    {
        static void Main(string[] args)
        {
            String sqlServerLogin = "";
            String password = "";
            String remoteSvrName = "";
            String fileLocation = "";
            String verboseLog = "";

            if (args == null || args.Length < 4)
            {
                Console.WriteLine("");
                Console.WriteLine("Usage : SQLJobScripter /u=<dbuser> /p=<password> /s=<dbinstance> /f=<filedirectory>");
                Console.WriteLine("");
                Console.WriteLine("Arguments can be used in any order, but all arguments must be present");
                Console.WriteLine("");
                Console.WriteLine(" /U=<dbuser>");
                Console.WriteLine("");
                Console.WriteLine(" /P=<password>");
                Console.WriteLine("");
                Console.WriteLine(" /S=<dbinstance>");
                Console.WriteLine("");
                Console.WriteLine(" /F=x:\\FileDirectory\\output.sql");
            }
            else
            {
                foreach (var arg in args)
                {
                    switch (arg.Substring(0, 3).ToUpper())
                    {
                        case "/U=":
                            sqlServerLogin = arg.Substring(3, arg.Length - 3);
                            break;
                        case "/P=":
                            password = arg.Substring(3, arg.Length - 3);
                            break;
                        case "/S=":
                            remoteSvrName = arg.Substring(3, arg.Length - 3);
                            break;
                        case "/F=":
                            fileLocation = arg.Substring(3, arg.Length - 3);
                            break;
                    }
                }

                var srvConn2 = new ServerConnection(remoteSvrName)
                {
                    LoginSecure = false,
                    Login = sqlServerLogin,
                    Password = password
                };
                var srv3 = new Server(srvConn2);
                var sb = new StringBuilder();
                var jv = srv3.JobServer;
                try
                {
                    foreach (Job jx in jv.Jobs)
                    {
                        var coll = jx.Script();
                        foreach (var str in coll)
                        {
                            sb.Append(str);
                            sb.Append(Environment.NewLine);
                        }
                    }
                    var fs = File.CreateText(fileLocation);
                    fs.Write(sb.ToString());
                    fs.Close();
                }
                catch (System.Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }  
            }
        }

    }
}
</dbinstance></password></dbuser></filedirectory></dbinstance></password></dbuser></code>

Points of Interest

As a DBA I am always on the lookout for new and different tools or techniques to generate my own object scripts for different purposes and after searching and finding very little on scripting out SQL Jobs in particular, this was a learning exercise for me to see how the SMO objects work and how useful they can be for a particluar need. I hope this helps to serve others in the community.