Script backup of replication setup of SQL Server by SSIS and SMO

Script backup of replication setup of 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”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.

Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication 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 replication along with its article 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 replication 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).

ReplicationBackupScriptTask

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Microsoft.SQLServer.Replication .NET Programming Interface

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 Microsoft.SqlServer.Replication;

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

string ServerName;

string UserName;

string Password;

string FolderDate;

string NetworkPath;

ServerName = "ServerName";

UserName = "Login";

Password = "Password";

FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);

NetworkPath = @"D:\"+ ServerName.Replace(‘\\’, ‘ ‘).ToString() + @"\Replicaiton\"+FolderDate+"\\";

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(NetworkPath);

ReplicationServer RS = new ReplicationServer(conn);

try

{

foreach (ReplicationDatabase RD in RS.ReplicationDatabases)

{

if (RD.HasPublications)

{

foreach (TransPublication TP in RD.TransPublications)

{

TextWriter tw = new StreamWriter(NetworkPath + "\\" + TP.Name.ToString() + ".sql");

tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));

tw.Close();

}

}

}

}

catch (Exception eh)

{

//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.

Here is the screen capture I have received after I ran it manually.

ScriptBackupReplicationPublisherSSISPackageRun

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.

Automatic script backup of mail profile in SQL Server by SSIS

Automatic script backup of mail profile in SQL Server by SSIS

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 “Automatic script backup of email operator in SQL Server by SSIS and SMO”, today I come up with article which generates the script for all mail profile in SQL Server instance by SSIS.

Mail Profile is one of the important configuration for DBA because of this mail profile 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 mail profile(s) 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 to generate the script of all mail profile 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 “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]

SELECT ‘–Create Account’ AS CMD

UNION ALL

SELECT ‘EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ”’ + m.name + ”’,

@description = ”’ + m.description + ”’,

@email_address = ”’ + m.email_address + ”’,

@replyto_address = ”’ + m.replyto_address + ”’,

@display_name = ”’ + m.display_name + ”’,

@mailserver_name = ”’ + s.servername + ”’,

@mailserver_type = ”’ + s.servertype + ”’,

@port = ”’ + cast(s.port as nvarchar) + ”’,

@username = ”’ + isnull(c.credential_identity,0) + ”’,

@password = ”x”,

@use_default_credentials = 0,

@enable_ssl = 0′ AS CMD

FROM msdb.dbo.sysmail_account m

LEFT OUTER JOIN msdb.dbo.sysmail_server s

ON m.account_id = s.account_id

LEFT OUTER JOIN master.sys.credentials c

ON s.credential_id = c.credential_id

UNION ALL

SELECT ‘–Create Profile’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ”’ + name + ”’,

@description = ”’ + isnull(description,’NULL’)+ ”’

‘ AS CMD

FROM msdb.dbo.sysmail_profile

UNION ALL

SELECT ‘–Create Link For Profile to Account’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ”’+ a.name +”’,

@account_name = ”’ + b.name + ”’,

@sequence_number = 1 ‘ as CMD

FROM msdb.dbo.sysmail_profile as a

JOIN msdb.dbo.sysmail_account as b on a.name = b.name

GO

[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2OLEDBSource

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “MailProfile.sql” which I already had. You can keep blank SQL file in your destination.

3FlatFileProperty

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “CMD” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

4SuccessfullyRanPackage

We have many different server/instance and need script for all mail profiles from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

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.

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.

Script all SQL Server Job automatically by SSIS and SMO

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.

1ScriptTaskEditor

Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

[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]

Here is the code I have written in “Main()” method of script editor.

[sourcecode language=”c”]
// 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);

try
{
string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
{
script ="";
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");
tw.Write(script);
tw.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
[/sourcecode]

Here is the screen capture of my script editor.

2ScriptEditor

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.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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