• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2293
  • Last Modified:

How to create MS SQL Maintenance Plan Programmatically?

Hi,

does anyone have a short example of code (let's say in C#) which can generate Maintenance Plan (could be SSIS package).
We have hundreds SQL Servers in our scope, and it would be much easier for us to automate deploy and backup of them. We do not want to use specialized tools like Quest LiteSpeed etc. But we would prefere to create C# command line program which will allow us to easily create Maintenance Plan (or SSIS package) which will be automatically deployed to target SQL 2005, 2008, 2008R2 Server and will backup and do basic maintenance tasks upon all online databases which are there. We have found some examples on : http://technet.microsoft.com/en-us/library/ms345167.aspx, but more related to SSIS packages than SQL Maintance Plans.

Any help will be widely appreciated.

Thanks

1
ITMokra
Asked:
ITMokra
  • 6
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
Here's one option - create your SSIS generic maintenance package for all your servers manually and use a config file for it then deploy to each of your SQL servers/computers

SSIS Package Configurations: http://msdn.microsoft.com/en-us/library/ms141682.aspx

How to: Deploy a Package: http://msdn.microsoft.com/en-us/library/ms140117.aspx
0
 
ITMokraAuthor Commented:
Seems like good idea, will try that.
Do you think it will work when deployed again to different SQL Server?
0
 
ITMokraAuthor Commented:
Or is it possible to create maintenance plans programmatically by using Microsoft.SqlServer.Management.DatabaseMaintenance namespace or am I wrong?

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.databasemaintenance(SQL.100).aspx

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ITMokraAuthor Commented:
Seems the namespace is working, successfully created Empty Maintenance Plan.

private void btnCreatePackage_Click(object sender, EventArgs e)
        {
            // Create server Connection Microsoft.SqlServer.Management.Common;
            ServerConnection conn = new ServerConnection("SQLServerName");
           
            // Create maintenance plan object with connection
            MaintenancePlan myMaintenance = new MaintenancePlan(conn);
            // Setup Maintenance plan properties                        
            myMaintenance.Description = "Backups";
            myMaintenance.Name = "SDOMOM BackupAllUsersDBs_Test";
            myMaintenance.BasePackage.Configurations.Add();
           
            /*
            //Create backup task
            DbMaintenanceBackupTask bckFull = new DbMaintenanceBackupTask();
            bckFull.BackupAction = Microsoft.SqlServer.Management.Smo.BackupActionType.Database;
            bckFull.BackupCompressionOption = Microsoft.SqlServer.Management.Smo.BackupCompressionOptions.Default;
            bckFull.BackupIsIncremental = false;
            bckFull.BackupDeviceType = Microsoft.SqlServer.Management.Smo.DeviceType.File;
            bckFull.BackupFileExtension = "bak";
            bckFull.DatabaseSelectionType = DatabaseSelection.User;
            bckFull.DestinationAutoFolderPath = @"C:\Temp\";
            bckFull.IgnoreDatabasesInNotOnlineState = true;
            bckFull.InDays = true;
            bckFull.DestinationCreationType = DestinationType.Auto;
            bckFull.ExistingBackupsAction = ActionForExistingBackups.Append;
            bckFull.LocalConnectionForLogging = "Local server connection";
            bckFull.TaskName = "Back Up Database (Full)";
            bckFull.CanUpdate("Back Up Database (Full)");
           
           
             */
            // Save Maintenance Plan
            myMaintenance.Save();
       }

Now I'm trying to create Backup Task, (should be subplan or sequence first, probably).
0
 
lcohanDatabase AnalystCommented:
"Do you think it will work when deployed again to different SQL Server? " - they must work 100% if designed/built to read the connection and other server/database speciffic parameters from config files that will be unique for each server.
0
 
ITMokraAuthor Commented:
I will try both ways because it seems that Microsoft.SqlServer.Management.DatabaseMaintenance is quite new, and not well documented. Actually I was able to easily create maintenance plan with c# but couldn't create db backup tasks. :-(
0
 
ITMokraAuthor Commented:
Thanks to guy here : http://blog.netnerds.net/2011/04/deploying-maintenance-plans-with-powershell-or-asp-net/
I've successfully manage it (sorry, code is not perfect and 100% complete and still needs some improvements) but it is working.

I've used same strategy as mentioned on netnerds.net.I've created 4 Maintenance Plan Templates, I'm replacing [SERVERNAME] & [BACKUPPATH] with variables which I'm getting from WebForm Textboxes. Nightmare was to schedule maintenance plan. :-( But this is done as well for now.

Page looks like this, I'm thinking about to rewrite it to Windows.Form.
Maintenance Plan Deployment from WEB
Code is here (I know it is not perfect, but for me enough now, function GetFrequencyInterval is not used yet, it will be used for reading data for schedule).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Data.SqlClient;
using System.Data;

namespace DeployMaintenancePlan
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        // Get Frequency interval to generate JobSchedule.FrequencyInterval... 

        static public int GetFrequencyInterval(bool monday, bool tuesday, bool wednesday, bool thursday, bool friday, bool saturday, bool sunday)
        {
            int res = 0;

            if (sunday)
                res |= 1 << 0;

            if (monday)
                res |= 1 << 1;

            if (tuesday)
                res |= 1 << 2;

            if (wednesday)
                res |= 1 << 3;

            if (thursday)
                res |= 1 << 4;

            if (friday)
                res |= 1 << 5;

            if (saturday)
                res |= 1 << 6;

            return res;
        }

        static public int GetFrequencyIntervalDaily()
        {
            return GetFrequencyInterval(true, true, true, true, true, true, true);
        }



        public string CreateSQLJobForMaintPlan(string strServerName, string strPackageName, string strSubplanName, string strPackagePath, Guid pkgId, Guid pkgSubPlanID, string strSQLVersion)
        {
            string jbName = strPackageName + "." + strSubplanName;
            string jbStpName = strSubplanName;
            // set the server name
            Server srv = new Server(strServerName);
            // Set the job server name
            JobServer jbsrv = srv.JobServer;
            // Define job object
            Job jb = new Job(jbsrv, jbName);

            
            // Craete the job on the instance of SQL Server Agent
            jb.Create();
            // Apply targeted server to already created job... otherwise it will fail!
            jb.ApplyToTargetServer(strServerName);

            // Define Jobstep Object variable by supplying the parent job and name arguments in the constructor
            JobStep jbstp = new JobStep(jb, jbStpName);
            jbstp.SubSystem = AgentSubSystem.Ssis;
            jbstp.Command = "/Server \"$(ESCAPE_NONE(SRVR))\" /SQL \"Maintenance Plans\\" + strPackageName + "\" /SET \"" + strPackagePath + ".Disable\";false";

            jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
            jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;

            jbstp.Create();

            // Define a jobSchedule object variable by supplying the parent job and name arguments in the constructor.
            JobSchedule jbsch = new JobSchedule(jb, jbName);

            // READ INI FILE WITH TIME CONFIGURATION FOR EACH JOB
            // THIS WILL READ VALUES IN THE INI FILE AND SCHEDULE A JOB ACCORDING TO IT.
            // FOLLOW WEBPAGES PATH WHERE YOUR MAINTPLAN TEMPLATES STORED, THERE SHOULD BE NAME WITH SAME NAME AND INI EXTENSION.

            // CREATE OBJECT iFile to setup INIFILE PATH
            INIFile ifile = new INIFile(HttpContext.Current.Server.MapPath(".\\" + strSQLVersion) + "\\" + strPackageName + "_Template.ini");
                // NEED TO EASILY CONFIGURE jbsch properties based on values from INI FILE.
                // -=--================================================================--=-

                string fType = ifile.Read("Schedule", "Type");
                int fTimeHour = Convert.ToInt16(ifile.Read("Schedule", "StartTimeHour"));
                int fTimeMinutes = Convert.ToInt32(ifile.Read("Schedule", "StartTimeMinutes"));
                int fTimeSeconds = Convert.ToInt32(ifile.Read("Schedule", "StartTimeSeconds"));
                int fFrequency = Convert.ToInt32(ifile.Read("Schedule", "Frequency"));
                int fReoccuranceFactor = Convert.ToInt32(ifile.Read("Schedule", "Interval"));

                

                // Set properties to define the schedule frequency and duration.
                // This has to be recreated as new function to be able to schedule based on request
                if (fType == "Daily")
                {
                    jbsch.FrequencyTypes = FrequencyTypes.Daily;
                    jbsch.FrequencyInterval = fFrequency;
                    TimeSpan ts1 = new TimeSpan(fTimeHour, fTimeMinutes, fTimeSeconds);
                    jbsch.ActiveStartTimeOfDay = ts1;
                    System.DateTime d = new System.DateTime(2011, 1, 1);
                    jbsch.ActiveStartDate = d;
                }
                else if (fType == "Weekly")
                {
                    jbsch.FrequencyTypes = FrequencyTypes.Weekly;
                    //jbsch.FrequencyInterval = GetFrequencyInterval();
                    jbsch.FrequencyRecurrenceFactor = fReoccuranceFactor;
                    jbsch.FrequencyInterval = fFrequency;
                    TimeSpan ts1 = new TimeSpan(fTimeHour, fTimeMinutes, fTimeSeconds);
                    jbsch.ActiveStartTimeOfDay = ts1;
                    System.DateTime d = new System.DateTime(2011, 1, 1);
                    jbsch.ActiveStartDate = d;
                }

            jbsch.Create();

            //jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
            //jbsch.FrequencySubDayInterval = 30;
            //TimeSpan ts1 = new TimeSpan(9, 0, 0);
            //jbsch.ActiveStartTimeOfDay = ts1;
            //TimeSpan ts2 = new TimeSpan(17, 0, 0);
            //jbsch.ActiveEndTimeOfDay = ts2;

            //Create job schedule on the instance of SQL Agent
            

            // ===========================================================================================
            // update maintenance plan with the schedule
            // THIS IS REALLY NECESSARY FOR LINKING SQL MAINTENANCE PLAN WITH JOB SCHEDULE
            // OTHERWISE IT WON'T WORK.
            // NEVER REMOVE JOB ITSELF AFTERWARDS, REMOVE SCHEDULE FROM MAINTENANCE PLAN FIRST.
            // SINCE IT IS LINKED, MSDB WILL BECAME POPULATED WITH WRONG DATA
            // ===========================================================================================
            string sp = "msdb.dbo.sp_maintplan_update_subplan";
            try
            {
                // Open SQL Server Connection
                SqlConnection conn = new SqlConnection("Server=" + strServerName + ";Database=MSDB;Integrated Security=SSPI");
                SqlCommand cmd = new SqlCommand(sp, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // Define sp_maintplan_update_subplan parameters and sql datatypes
                SqlParameter Param1 = cmd.Parameters.Add("@subplan_id", SqlDbType.UniqueIdentifier);
                SqlParameter Param2 = cmd.Parameters.Add("@plan_id", SqlDbType.UniqueIdentifier);
                SqlParameter Param3 = cmd.Parameters.Add("@name", SqlDbType.NVarChar);
                SqlParameter Param4 = cmd.Parameters.Add("@description", SqlDbType.NVarChar);
                SqlParameter Param5 = cmd.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier);
                SqlParameter Param6 = cmd.Parameters.Add("@schedule_id", SqlDbType.Int);
                SqlParameter Param7 = cmd.Parameters.Add("@allow_create", SqlDbType.Bit);
                SqlParameter Param8 = cmd.Parameters.Add("@msx_job_id", SqlDbType.UniqueIdentifier);


                // Setup Stored Procedure Parameters
                Param1.Value = pkgSubPlanID;
                Param2.Value = pkgId;
                Param3.Value = strSubplanName;
                Param4.Value = null;
                Param5.Value = jb.JobID;
                Param6.Value = jbsch.ID;
                Param7.Value = 1;
                Param8.Value = null;

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return "Successfully Linked Job To Your Maintenance Plan" + strPackageName + "<br>With following schedule : <br>";

            }
            catch (SqlException SQLex)
            {
                return "<font color = \"red\"><b>" + SQLex.Message.ToString() + "</font></b><br>";
                // Write something here
            }



        }

        protected void Page_Load(object sender, EventArgs e)
        {
             // Check if the user is already loged in or not
            if ((Session["Check"] != null) && (Convert.ToBoolean(Session["Check"]) == true))
            {
                // If User is Authenticated then moved to a main page
                if (User.Identity.IsAuthenticated)
                    Response.Redirect("default.aspx");
            }
            else
            {
                Response.Redirect("login.aspx");
            }

            /*
            MessageBox msgbox = new MessageBox(HttpContext.Current.Server.MapPath("msgbox.tpl"));
            msgbox.SetTitle("Welcome");
            msgbox.SetIcon("msg_icon_1.png");
            msgbox.SetMessage("<font color=\"red\"><b>Please be carefull when using this tool!</b></font>.<br><br>This tool is using GROUPHC\\grpsrvcsqladmin account to create Maintenance plans and jobs!<BR><BR>If you are not sure, contact SDODB Team!");
            msgbox.SetOKButton("msg_button_class");
            msgboxpanel.InnerHtml = msgbox.ReturnObject();
            */
            
        }

        public string CreatePackage(string strServerName, string strBackupPath, string strFileName, string strSqlVersion, string strFolderName)
        {
            try
            {
                // Read your package template
                // Folders are in same location as ASP.Net files
                String packageContents = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(".\\" + strSqlVersion) + "\\" + strFileName);
                // Replace your placeholders with the server and backup paths
                packageContents = packageContents.Replace("[SERVERNAME]", strServerName);
                packageContents = packageContents.Replace("[BACKUPPATH]", strBackupPath + "\\" + strFolderName);

                // Setup path to write your temporary dtsx file
                String strDTSXPath = HttpContext.Current.Server.MapPath(".\\Temporary\\");
                String strPkg = strDTSXPath + "output.dtsx";

                // Write the new package to the disk with UTF8 Encoding
                System.IO.File.WriteAllText(strPkg, packageContents, System.Text.Encoding.UTF8);

                // Create your SSIS Objects
                Application app = new Application();
                Package LoadedPkg = new Package();
                String strpkgName = "";

                // Load your new temporary package from disk ...
                LoadedPkg = app.LoadPackage(strPkg, null, true);
                strpkgName = LoadedPkg.Name;

                if (app.ExistsOnSqlServer("\\\\Maintenance Plans\\" + strpkgName, strServerName, null, null))
                {
                    return "<font color=\"red\"><b>Package " + strpkgName + " does already exist on server : " + strServerName + "</font></b><br>";
                    
                }
                else
                {
                    // Deploy to sql server 
                    app.SaveToSqlServerAs(LoadedPkg, null, "\\\\Maintenance Plans\\" + strpkgName, strServerName, null, null);

                    // Return result as the string to 
                    String strResulttext = "<font color = \"green\"><b>" + strpkgName + " deployed to " + strServerName + " successfully.</b></font><br/>";
                    return strResulttext;
                }
            }
            catch (Exception e)
            {
                return "<b><font color = \"red\">" + e.Message.ToString() + "</font></b><br>";
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            resultsLabel.Text = "";
            String SQLVersion = versionDDL.SelectedValue;
            String backupPath = tbBackupPath.Text.ToString();
            String ServerName = tbServerName.Text.ToString();

            if (SQLVersion == "SQL2000")
            {
                resultsLabel.Text = "<B><FONT COLOR=\"RED\">SQL 2000 Version is unsupported!</B></FONT>";

            }
            else
            {
                if ((backupPath == "") || (ServerName == ""))
                {
                    resultsLabel.Text = "";
                    resultsLabel.Text = "<B><FONT COLOR=\"RED\">Please write down ServerName and Path!</B></FONT>";
                }
                else
                {
                    resultsLabel.Text += "Please wait it will take a while ... <br><br>";
                    try
                    {                        
                        System.IO.Directory.CreateDirectory(backupPath + "\\User");
                        resultsLabel.Text += backupPath + "\\User has been successfully created...<br/>";
                        System.IO.Directory.CreateDirectory(backupPath + "\\System");
                        resultsLabel.Text += backupPath + "\\System has been successfully created...<br/>";
                    }
                    catch (IOException ex)
                    {
                        resultsLabel.Text += "There was a problem with creating backup folders:<br/><br/>";
                        resultsLabel.Text += "<b><font color = \"red\">" + ex.Message.ToString() + "</font></b><br/>";
                    }
                    // Log your work to siple label...
                    resultsLabel.Text += CreatePackage(ServerName, backupPath, "SDOMOM BackupAllUsersDBs_Template.dtsx", SQLVersion, "User");
                    resultsLabel.Text += CreatePackage(ServerName, backupPath, "SDOMOM BackupSystemDBs_Template.dtsx", SQLVersion, "System");
                    resultsLabel.Text += CreatePackage(ServerName, backupPath, "SDOMOM BackupAllTrLogs_Template.dtsx", SQLVersion, "User");
                    resultsLabel.Text += CreatePackage(ServerName, backupPath, "SDOMOM RegularMaintenance_Template.dtsx", SQLVersion, null);
                    
                    try
                    {
                        System.IO.File.Delete(HttpContext.Current.Server.MapPath(".\\Temporary\\") + "output.dtsx");
                    }
                    catch (IOException IOEx)
                    {
                        resultsLabel.Text += "<font color=\"red\"><b>" + IOEx.Message.ToString() + "</b></font><br />";
                    }

                    btnSchedule.Visible = true;

                }
            }
            
        }

        protected void HelpBackupPath_Click(object sender, ImageClickEventArgs e)
        {
            MessageBox msgbox = new MessageBox(HttpContext.Current.Server.MapPath("msgbox.tpl"));
            msgbox.SetTitle("Help");
            msgbox.SetIcon("question.png");
            msgbox.SetMessage("Use ONLY UNC PAHT... <BR><B>Local Path Does Not Work Yet!</B>");
            msgbox.SetOKButton("msg_button_class");
            msgboxpanel.InnerHtml = msgbox.ReturnObject();
        }

        protected void HelpServerName_Click(object sender, ImageClickEventArgs e)
        {
            MessageBox msgbox = new MessageBox(HttpContext.Current.Server.MapPath("msgbox.tpl"));
            msgbox.SetTitle("Help");
            msgbox.SetIcon("question.png");
            msgbox.SetMessage("Write down SQL Server name. <br> Or SQL Server Name plus Instance name. <BR><b>Eg. :<br> MYSERVERNAME\\MYINSTANCENAME<BR>MYSERVERNAME</b><br><br>Take a notice, that <b>GROUPHC\\grpsrvcsqladmin</b> account is in use, and therefore this account must have permissions<br>to your destination folder!!!");
            msgbox.SetOKButton("msg_button_class");
            msgboxpanel.InnerHtml = msgbox.ReturnObject();
        }

        protected void HelpSQLVersion_Click(object sender, ImageClickEventArgs e)
        {
            MessageBox msgbox = new MessageBox(HttpContext.Current.Server.MapPath("msgbox.tpl"));
            msgbox.SetTitle("Help");
            msgbox.SetIcon("question.png");
            msgbox.SetMessage("Please Ensure that You are Deploying proper version of DTS Packages<BR /><br>SQL2008R2 is setup with Compression!<br>Backup Logs Are Created in Same folder like Backup files.<br><br>SQL2005 Template Doesn't have Compression Enabled!");
            msgbox.SetOKButton("msg_button_class");
            msgboxpanel.InnerHtml = msgbox.ReturnObject();
        }

        protected void btnSchedule_Click(object sender, EventArgs e)
        {
            // Setup Variables
            string strsqlFolder;
            string strsqlServer;
            string strsqlUser;
            string strsqlPassword;
            string strsqlVersion;
            // Clear output
            lblScheduleResult.Text = "";


            Application ssisApplication;
            PackageInfos sqlPackages;
            

            strsqlFolder = "\\Maintenance Plans\\";
            strsqlServer = tbServerName.Text.ToString();
            strsqlUser = String.Empty;
            strsqlPassword = String.Empty;
            strsqlVersion = versionDDL.Text.ToString();

            ssisApplication = new Application();

            sqlPackages = ssisApplication.GetPackageInfos(strsqlFolder, strsqlServer, strsqlUser, strsqlPassword);
            // ENUMERATE ALL PACKAGES
            // CREATE OBJECT sqlPackage
            foreach (PackageInfo sqlPackage in sqlPackages)
            {
                // Create variable to get package path
                string strPackagePath = strsqlFolder + sqlPackage.Name;
                // Create object Package
                Package pkgOut = new Package();
                // Load package from Server
                pkgOut = ssisApplication.LoadFromSqlServer(strPackagePath,strsqlServer, null, null, null);

                // ENUMERATE ALL EXECUTABLES IN PACKAGE
                foreach (Executable exe in pkgOut.Executables)
                {
                    // FIND A SEQUENCE {WORKING WITH ONE SUBPLAN ONLY}... Must be in template
                    if (exe is Sequence)
                    {
                        //Response.Write("Sequence Found");
                        Sequence seq = (Sequence)exe;
                        // Create guid type from package id, and subplan id.
                        Guid guidpackageID = new Guid(pkgOut.ID);
                        Guid guidsubplanID = new Guid(seq.ID);
                        // Write result of the function called CreateSQLJobForMaintenancePlan to label.                        
                        lblScheduleResult.Text += CreateSQLJobForMaintPlan(strsqlServer, pkgOut.Name, seq.Name, seq.GetPackagePath(), guidpackageID, guidsubplanID, strsqlVersion);
                    }
                }
            }


        }
    }
}

Open in new window

0
 
ITMokraAuthor Commented:
It was solved exactly how I've expected.
It is completely meets our needs.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now