logicsqldmo.cs

使用SQL Server Agent 自动对SQLServer数据库进行自动备份

//'---------> Created by Joshy George <----------
//'---------> Use this as you like, no copyright, no restrictions <----------
//'---------> All I want is to Give, because I have Received  <----------
//'---------> So much help from others.Thank You ! <----------

///* This is my sample program for Creating SQL Server Jobs 
// * Feel free to change or modify the code, and use it,it sure would be nice to
// * give me credit for something if you do use this in something that you make for yourself
// * or others, but what you do if up to you.
// * Questions or Comments may be addressed to Spot <[e-mail]>, 
// * and you can find more  information on my web-site (http://www.joshygeo.tk).  
// * This code is free to use modify and distribute, so long as what your doing with it is also free, 
// * I'd like to here about any interesting modifications that other programmers come up with.

//'---------> Email:-  [e-mail] <----------
//'---------> WebSite:- www.joshygeo.tk <----------

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using SQLDMO;
using System.IO;//This is for File Functions
namespace dbMaintain.BL
{
    public class BusinessLogicLayer
    {
        public void dIsplayServerList(ComboBox cboListName)
        {//This is for server listing.

            try
            {
                SQLDMO.Application oSQLServerDMOApp = new SQLDMO.Application();
                Info.informationLayer info = new Info.informationLayer();
                
                SQLDMO.NameList oNameList;
                oNameList = oSQLServerDMOApp.ListAvailableSQLServers();
                for (int intIndex = 0; intIndex <= oNameList.Count - 1; intIndex++)
                {
                    if (oNameList.Item(intIndex as object) != null)
                    {
                        cboListName.Items.Add(oNameList.Item(intIndex).ToString());
                    }
                }
                if (cboListName.Items.Count > 0) cboListName.SelectedIndex = 0;
                else cboListName.Text = "(Local)";
            }
            catch 
            {
                
            }

        }
        public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)
        {//This is for Database listing.
            try
            {
                SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();

                cboDatabase.Items.Clear();
                SQLServer.Connect(info.strServerName,info.strLoginName,info.strPwd);
                foreach (SQLDMO.Database db in SQLServer.Databases)
                {
                    if (db.Name != null)
                        cboDatabase.Items.Add(db.Name);
                }
                cboDatabase.Sorted = true;
                if (cboDatabase.Items.Count == 0)cboDatabase.Text = "<No databases found>";
            }
            catch (Exception err)
            {
                info.ErrorMessageDataLayer = err.Message;

            }
        }
        public void CreateJob_Sql(Info.informationLayer info)
        {// This is for Create Job on SQl server agent
            {
                try
                {
                    SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
                    SQLDMO.Job SQLJob = new SQLDMO.Job();
                    SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();
                    // Conneting sql server
                    SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);

                    switch (SQLServer.JobServer.Status)
                    {
                        case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped:
                            SQLServer.JobServer.Start();
                            SQLServer.JobServer.AutoStart = true;
                            break;
                    }
                    //set db name
                    SQLJob.Name = info.strDatabaseName;
                    SQLJob.Description = "Check and Backup" + info.strDatabaseName;

                    SQLServer.JobServer.Jobs.Add(SQLJob);
                    SQLJob.Category = "Database Maintenance";

                    SQLDMO.JobStep aJobStep = new SQLDMO.JobStep();

                    aJobStep.Name = "Step 2: Backup the Database";
                    aJobStep.StepID = 1;

                    aJobStep.DatabaseName = info.strDatabaseName;

                    aJobStep.SubSystem = "TSQL";
                    //------>>> If BackUp Folder is Not Found then create BackUp Folder.
                                    
                     string   DirectoryName = "D:\\BackUp";
                     if (Directory.Exists(DirectoryName)==false)
                     {
                         System.IO.Directory.CreateDirectory(DirectoryName);
                     }
                    //------>>>
                    string sExt;
                    //set command line arguments for server agent.
                    sExt="EXEC master.dbo.xp_sqlmaint '-S " + info.strServerName + " -U " + info.strLoginName + " -P " + info.strPwd + "  -D " + info.strDatabaseName + " -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB D:\\Backup  -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt D:\\Backup\\BackDB_Checks.txt'";
                    aJobStep.Command = sExt;
                    aJobStep.OnSuccessAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithSuccess;
                    aJobStep.OnFailAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure;
                    SQLJob.JobSteps.Add(aJobStep);
                    SQLJob.ApplyToTargetServer(info.strServerName);
                    aJobStep.DoAlter();
                    SQLJob.Refresh();
                    aJobStep.Refresh();

                }
                catch (Exception Err)
                {
                    info.ErrorMessageDataLayer = Err.Message;
                }
            }
        }
        public void CreateShedule_Sql(Info.informationLayer info)
        {
            try
            {
                //it will take bkp every week 2 day
                SQLDMO.Job SQLJob = new SQLDMO.Job();

                SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
                SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();

                SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);
                SQLJob = SQLServer.JobServer.Jobs.Item(info.strDatabaseName);

                // create a new JobSchedule object
                SQLSchedule.Name = "Weekly Backup";
                SQLSchedule.Schedule.FrequencyType = SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Weekly;
                SQLSchedule.Schedule.FrequencyInterval = 2;
                SQLSchedule.Schedule.FrequencyRecurrenceFactor = 2; 

                // // set backup start date
                SQLSchedule.Schedule.ActiveStartDate = info.intStartDate;
                SQLSchedule.Schedule.ActiveStartTimeOfDay = info.intStartTime;
                ////  this schedule has no end time or end date

                SQLSchedule.Schedule.ActiveEndDate = 99991231;
                SQLSchedule.Schedule.ActiveEndTimeOfDay = 235959;

                ////  add the schedule to the Job
                SQLJob.BeginAlter();
                SQLJob.JobSchedules.Add(SQLSchedule);
                SQLJob.DoAlter();
                //SQLJob.JobSchedules.Refresh();
                info.ErrorMessageDataLayer = "New Sql Job [Databasename= " + info.strDatabaseName + " ]Sucessfully Created.  ";
            }
            catch (Exception err)
            {
                info.ErrorMessageDataLayer = err.Message;

            }
        }
    }
}