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.
Originally started out as an Oracle developer, I moved on to become an Oracle DBA, then became certified on Oracle. Later on I had the opportunity to work on some T-SQL projects in parallel with PL/SQL. After working with SQL Server for some time, I now primary find myself dealing almost exclusively with SQL Server and the Microsoft toolkits/SDKs. I am gainfully employed in the financial services industry as a Senior Data/Database Architect specializing in performance tuning, T-SQL code optimization and evangelizing the use of CLR stored procedures, functions, triggers and UDTs, where appropriate.