Automatic SQL Server Backup Utility using Sqlserver Agent

Introduction

It is a sample C# (VS2005) 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 use VB.NET and installed SQL Server 2000 (any edition or Client Components for SQL Server 2000.)

SQLDMO (Which installed always bt MS SQL Server 2000 or MS SQL Server Client Tools).

1. First enter your SQL Server username and password oncorresponding Text Box.
2. Set backup Start date & Backup Time.
3. After Finishing this then please check manually it will working or not.
4. Manual working procedure
   1. Run Sql sever enterprise Manager.
   2. Select management Option.
   3. Open Sql server agent.
   4. Open Jobs window.
   5. Check whether job item exist 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 then check folder "D:\backup" bkp file created or not.

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
    {
        // Create an instance of SQLDMO.Application
        SQLDMO.Application oSQLServerDMOApp = new SQLDMO.Application();

        // Create an instance of Info.informationLayer
        Info.informationLayer info = new Info.informationLayer();

        // Create a NameList to store available SQL servers
        SQLDMO.NameList oNameList;

        // List available SQL servers
        oNameList = oSQLServerDMOApp.ListAvailableSQLServers();

        // Loop through the list of available SQL servers
        for (int intIndex = 0; intIndex < oNameList.Count; intIndex++)
        {
            // Check if the item at the current index is not null
            if (oNameList.Item(intIndex as object) != null)
            {
                // Add the SQL server name to the ComboBox
                cboListName.Items.Add(oNameList.Item(intIndex).ToString());
            }
        }

        // Set the selected index of the ComboBox
        if (cboListName.Items.Count > 0)
        {
            cboListName.SelectedIndex = 0;
        }
        else
        {
            // If no items were added, set a default text
            cboListName.Text = "(Local)";
        }
    }
    catch (Exception ex)
    {
        // Handle exceptions here or log them
        Console.WriteLine("Error: " + ex.Message);
    }
}

Available databases

public void DisplayDatabases(ComboBox cboDatabase, Info.informationLayer info)
{
    try
    {
        // Create an instance of SQLDMO._SQLServer
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();

        // Clear existing items in the ComboBox
        cboDatabase.Items.Clear();

        // Connect to the SQL Server using information from the 'info' object
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);

        // Loop through the databases on the SQL Server
        foreach (SQLDMO.Database db in SQLServer.Databases)
        {
            // Check if the database name is not null and add it to the ComboBox
            if (db.Name != null)
            {
                cboDatabase.Items.Add(db.Name);
            }
        }

        // Sort the items in the ComboBox
        cboDatabase.Sorted = true;

        // Set a default text if no databases were found
        if (cboDatabase.Items.Count == 0)
        {
            cboDatabase.Text = "<No databases found>";
        }
    }
    catch (Exception err)
    {
        // Handle exceptions by setting an error message in the 'info' object
        info.ErrorMessageDataLayer = err.Message;
    }
}

Create Job on Server Agent

public void CreateJobSql(Info.informationLayer info)
{
    try
    {
        // Create an instance of SQLDMO._SQLServer
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();

        // Create a new SQL job
        SQLDMO.Job SQLJob = new SQLDMO.Job();

        // Create a new SQL job schedule
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();

        // Connect to the SQL Server using information from the 'info' object
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);

        // Check the status of the SQL Server Job Service
        switch (SQLServer.JobServer.Status)
        {
            case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped:
                SQLServer.JobServer.Start();
                SQLServer.JobServer.AutoStart = true;
                break;
        }

        // Set the name and description of the SQL job
        SQLJob.Name = info.strDatabaseName;
        SQLJob.Description = "Check and Backup " + info.strDatabaseName;

        // Add the SQL job to the SQL Server's job collection
        SQLServer.JobServer.Jobs.Add(SQLJob);

        // Set the category of the SQL job
        SQLJob.Category = "Database Maintenance";

        // Create a new job step
        SQLDMO.JobStep aJobStep = new SQLDMO.JobStep();

        // Set properties for the job step
        aJobStep.Name = "Step 2: Backup the Database";
        aJobStep.StepID = 1;
        aJobStep.DatabaseName = info.strDatabaseName;
        aJobStep.SubSystem = "TSQL";

        // Check if the backup folder exists, and create it if not
        string DirectoryName = "D:\\BackUp";
        if (!Directory.Exists(DirectoryName))
        {
            System.IO.Directory.CreateDirectory(DirectoryName);
        }

        // Define the command for the job step
        string 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;

        // Set actions for job step on success and failure
        aJobStep.OnSuccessAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithSuccess;
        aJobStep.OnFailAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure;

        // Add the job step to the SQL job
        SQLJob.JobSteps.Add(aJobStep);

        // Apply the job to the target server
        SQLJob.ApplyToTargetServer(info.strServerName);

        // Alter the job step and refresh the job and job step
        aJobStep.DoAlter();
        SQLJob.Refresh();
        aJobStep.Refresh();
    }
    catch (Exception Err)
    {
        // Handle exceptions by setting an error message in the 'info' object
        info.ErrorMessageDataLayer = Err.Message;
    }
}

Create Job shedule on server Agent

public void CreateScheduleSql(Info.informationLayer info)
{
    try
    {
        // Create a new SQLJob object
        SQLDMO.Job SQLJob = new SQLDMO.Job();

        // Create an instance of SQLDMO._SQLServer
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();

        // Create a new JobSchedule object
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();

        // Connect to the SQL Server using information from the 'info' object
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);

        // Retrieve the existing SQL job by name
        SQLJob = SQLServer.JobServer.Jobs.Item(info.strDatabaseName);

        // Create a new JobSchedule object
        SQLSchedule.Name = "Weekly Backup";

        // Set the schedule to run weekly, every 2nd day
        SQLSchedule.Schedule.FrequencyType = SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Weekly;
        SQLSchedule.Schedule.FrequencyInterval = 2;
        SQLSchedule.Schedule.FrequencyRecurrenceFactor = 2;

        // Set the active start date and time
        SQLSchedule.Schedule.ActiveStartDate = info.intStartDate;
        SQLSchedule.Schedule.ActiveStartTimeOfDay = info.intStartTime;

        // Set the schedule to have no end date or end time
        SQLSchedule.Schedule.ActiveEndDate = 99991231;
        SQLSchedule.Schedule.ActiveEndTimeOfDay = 235959;

        // Add the schedule to the SQL job
        SQLJob.BeginAlter();
        SQLJob.JobSchedules.Add(SQLSchedule);
        SQLJob.DoAlter();

        // Set a success message
        info.ErrorMessageDataLayer = "New SQL Job [Databasename= " + info.strDatabaseName + " ] successfully created.";
    }
    catch (Exception err)
    {
        // Handle exceptions by setting an error message in the 'info' object
        info.ErrorMessageDataLayer = err.Message;
    }
}

Syntax (SQL Server 2000)

xp_sqlmaint 'switch_string' [
    [-S server_name[\instance_name]] 
    [-U login_ID] 
    [-P password]
    {
        [-D database_name | -PlanName name | -PlanID guid]
        [-Rpt text_file]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>]]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkCat]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {
                -BkUpDB [backup_path] | -BkUpLog [backup_path]
            }
            {
                -BkUpMedia 
                {
                    DISK [
                        [-DelBkUps <time_period>] 
                        [-CrBkSubDir ] 
                        [-UseDefDir ]
                    ]
                    | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    ]
    time_period number [minutes | hours | days | weeks | months]
]

Syntax (SQL Server 7.0)

sqlmaint [-?] | [
    [-S server] 
    [-U login_ID [-P password]]
    {
        [-D database_name | -PlanName name | -PlanID guid] 
        [-Rpt text_file [-DelTxtRpt <time_period>]]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>]]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkTxtAl]
        [-CkCat]
        [-UpdSts]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {
                -BkUpDB [backup_path] 
                | -BkUpLog [backup_path]
            }
            {
                -BkUpMedia 
                {
                    DISK [
                        [-DelBkUps <time_period>] 
                        [-CrBkSubDir ] 
                        [-UseDefDir ]
                    ] 
                    | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    ]
]