Click here to Skip to main content
Click here to Skip to main content

Automatic SQL Server Backup Utility using sqlserveragent

, 5 Oct 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
Automatic SQL Server Backup Utility using sqlserveragent
Sample Image - Sql_backup_utility.jpg

Introduction

This is a sample C# (Visual Studio 2005) application for Automatic SQL Server Backup Utility using sqlserveragent. I have used SQL-DMO DLL. This article will show you how to create a automatic backup in SQL Server 2000.

This code should work on any PC using VB.NET and installed SQL Server 2000 (any edition or Client Components for SQL Server 2000).

SQLDMO (which is always installed between Microsoft SQL Server 2000 or Microsoft SQL Server Client Tools).

To Do

  1. First enter your SQL Server username and password in the corresponding Text Box
  2. Set backup Start date & Backup Time
  3. After finishing this, please check manually whether it will working or not

Manual Working Procedure

  1. Run SQL Server Enterprise Manager
  2. Select management option
  3. Open SQL Server agent
  4. Open Jobs window
  5. Check whether job item exists or not
  6. Right click on newly created job item, then we will get one
  7. Popup menu, then select start job
  8. After finish the job, check in the folder D:\backup whether the backup file has been created or not

Important Functions

Add Reference to SQL-DMO DLL

You can do this by right clicking the project in Solution Explorer, then selecting 'Add Reference', COM components and the latest version of "Microsoft SQLDMO Object Library".

Available Server

public void dIsplayServerList(ComboBox cboListName)
{
    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 
    {
                
    }
}

Available Databases

public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)
{
        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 found databases>";
        }
        catch (Exception err)
        {
            info.ErrorMessageDataLayer = err.Message;
        }
}

Create Job on Server Agent

public void CreateJob_Sql(Info.informationLayer info)
{
    {
        try
        {
            SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
            SQLDMO.Job SQLJob = new SQLDMO.Job();
            SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();

            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;
            }

            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;
            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;
        }
    }
}

Create Job Schedule on Server Agent

public void CreateShedule_Sql(Info.informationLayer info)
{
    try
    {
        //it will take backup 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; 

        // // start on Feb18, 2000 - at 12.55
        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 + " ]Successfully Created.  ";
    }
    catch (Exception err)
    {
        info.ErrorMessageDataLayer = err.Message;
    }
}

History

  • 5th October, 2006: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Joshy_geo
Web Developer
India India
I'm Joshy George.I finished my Post Graduation in Computer Application,i'm from the great 'Gods Own Country', Kerala, India. Now I'm working in Infopark Kochin, as a Sr.software Engineer.my specialised areas are C# ,Asp.Net ,Vb6,smart card application,Tapii,Comm ctrls.

Comments and Discussions

 
QuestionError PinmemberAhmad Halabi30-Jul-13 0:13 
GeneralMy vote of 4 PinmemberChien Thanh4-Apr-12 17:04 
Generalit is not working in sql server 2008 too PinmemberWatcharakorn Wanich22-May-11 6:03 
Questionhow to pass windows authentication parameter Pinmemberkirankkk200929-Jan-10 18:42 
how to pass windows authentication parameter to backup databaseConfused | :confused: Confused | :confused:
Generalgood one, thanks Pinmemberitayl27-May-09 12:14 
GeneralError : Cannot start this job because it doesn't not have any step PinmemberDungVinh24-Sep-08 18:23 
Questionsqldmo errors Pinmemberanahita_m19-Aug-07 21:44 
Generalit is not working in sql server 2005 Pinmembermanish ojha16-May-07 8:28 
QuestionWill this work in Sql2005? PinmemberAutolibSe4-Dec-06 10:01 
GeneralSql Server Pinmembersathishtl00714-Nov-06 17:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 5 Oct 2006
Article Copyright 2006 by Joshy_geo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid