Automate the SQL Server backup in C#

Question

I am writing a Windows Forms application to backup my SQL Server databases. I am using the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SQL_Server_DB_Backup_and_Restore
{
    public partial class Form1 : Form
    {
        private SqlConnection conn;
        private SqlCommand command;
        private SqlDataReader reader;
        string sql = "";
        string connectionString = "";

        public Form1()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dlg = new FolderBrowserDialog();

            if(dlg.ShowDialog()==DialogResult.OK)
            {
                txtBackupFileLocation.Text = dlg.SelectedPath;
            }
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                connectionString = "Data Source= " + txtDataSource.Text + "; User Id=" + txtUserId.Text + "; Password=" + txtPassword.Text + "";
                conn = new SqlConnection(connectionString);
                conn.Open();
                //sql = "EXEC sp_databases";
                sql = "SELECT * FROM sys.databases d WHERE d.database_id>4";
                command = new SqlCommand(sql, conn);
                reader = command.ExecuteReader();
                cmbDatabases.Items.Clear();

                while(reader.Read())
                {
                    cmbDatabases.Items.Add(reader[0].ToString());
                }

                txtDataSource.Enabled = false;
                txtUserId.Enabled = false;
                txtPassword.Enabled = false;
                btnConnect.Enabled = false;
                btnDisconnect.Enabled = true;

                btnBackup.Enabled = true;
                btnRestore.Enabled = true;
                cmbDatabases.Enabled = true;
            }

            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnDisconnect_Click(object sender, EventArgs e)
        {
            txtDataSource.Enabled = true;
            txtUserId.Enabled = true;
            txtPassword.Enabled = true;
            cmbDatabases.Enabled = false;
            btnBackup.Enabled = false;
            btnRestore.Enabled = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            btnDisconnect.Enabled = false;
            cmbDatabases.Enabled = false;
            btnBackup.Enabled = false;
            btnRestore.Enabled = false;
        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
            try
            {
                if(cmbDatabases.Text.CompareTo("")==0)
                {
                    MessageBox.Show("Please select a database");
                    return;
                }
                conn = new SqlConnection(connectionString);
                conn.Open();
                sql = "BACKUP DATABASE " + cmbDatabases.Text + " TO DISK = '" + txtBackupFileLocation.Text + "\\" + cmbDatabases.Text + "-" + DateTime.Now.Ticks.ToString() + ".bak'";
                command = new SqlCommand(sql, conn);
                command.ExecuteNonQuery();
                MessageBox.Show("Database backup is completed successfully");
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

Right now I have to press a button to take backup. How can I automate the process so that my application may take the backup weekly?

Answer 1

MS SQL Server has a functionality to automate/scheduled your database backup. On your Object Explorer. Go to

Management --> Maintenance Plan - > New Maintenance Plan
and on Maintenance Plan Tasks drag Back up Database Task.
And click the calendar icon above to set the time of backup. This is for SQL Server 2008 above.