Automatic script backup of email operator in SQL Server by SSIS and SMO

Automatic script backup of email operator in SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and today I come up with article which generates the script for all email operator in SQL Server instance by SSIS and SMO.

Email Operator is one of the important configuration for DBA because of this operator I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same operator and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of all email operator of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

1Package

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.

[sourcecode language=”C”]

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;

[/sourcecode]

Now here is the code you have to place in your “Main” method of script window.

[sourcecode language=”C”]

// TODO: Add your code here

StringCollection sc = new StringCollection();

ScriptingOptions so = new ScriptingOptions();

so.IncludeDatabaseContext = true;

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

ServerName = "ServerName";
UserName = "Login";
Password = "Password";
FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);

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("D:\\MyServerName\\Operators\\" + FolderDate.ToString());
try
{
string script = "";
string OperatorName;

//Loop over all the jobs
foreach (Operator O in srv.JobServer.Operators)
{
//Output name in the console
Console.WriteLine(O.Name.ToString());

OperatorName = O.Name.ToString();
sc = O.Script(so);

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

//Generate the file
TextWriter tw = new StreamWriter("D:\\MyServerName\\Operators\\" + FolderDate.ToString() + "\\" + OperatorName.Replace(‘:’, ‘_’).ToString() + ".sql");
tw.Write(script);
tw.Close();

//Make the string blank again
script ="";
}
}
catch
{
//MessageBox.Show(eh.ToString());
}

Dts.TaskResult = (int)ScriptResults.Success;
[/sourcecode]

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.