Backup and Restore Sql Server database in C#

This tutorial explains how to create a Sql Server Backup file in C# and also how to restore the

database. We use a Backup file and restore database when our database becomes

corrupted or crashes. So let's have a pratical example of  how to create it in C#, this example is

developed in Visual Studio 2010.

 First, we create this Windows Form and we declare the connectionString of the database.

SqlConnection con = new SqlConnection(WindowsFormsApplication11.Properties.Settings.Default.database1ConnectionString);

We start by the Backup, in BrowseButton click event we add this code:

private void browseButton_Click(object sender, EventArgs e)
{
    FolderBrowserDialog dlg = new FolderBrowserDialog();
    if (dlg.ShowDialog() == DialogResult.OK)
    {
         textBox1.Text = dlg.SelectedPath;
         BackupButton.Enabled = true;
    }
}

In BackupButton click event we add this code:

private void BackupButton_Click(object sender, EventArgs e)
{
    string database = con.Database.ToString();
    try
    {
        if(textBox1.Text==string.Empty)
        {
            MessageBox.Show("please enter backup file location");
        }
        else
        {
            string cmd = "BACKUP DATABASE [" + database + "] TO DISK='" + textBox1.Text + "\\" + "database" + "-" + DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss") + ".bak'";

            using(SqlCommand command = new SqlCommand(cmd,con))
            {
                if(con.State!=ConnectionState.Open)
                {
                    con.Open();
                }
                command.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("database backup done successefully");
                BackupButton.Enabled = false;
            }
       }

    }
    catch
    {

    }
}

Then, let's handle Restore database. In BrowseButton click event we add this code:

private void Browsebutton2_Click(object sender, EventArgs e)
{
    OpenFileDialog dlg = new OpenFileDialog();
    dlg.Filter = "SQL SERVER database backup files|*.bak";
    dlg.Title = "Database restore";
    if (dlg.ShowDialog() == DialogResult.OK)
    {
        textBox2.Text = dlg.FileName;
        restoreButton.Enabled = true;
    }
}

Finally, in RestoreButton click event we put this code:

private void restoreButton_Click(object sender, EventArgs e)
{
    string database = con.Database.ToString();
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }
    try
    {
        string sqlStmt2 = string.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        SqlCommand bu2 = new SqlCommand(sqlStmt2, con);
        bu2.ExecuteNonQuery();

        string sqlStmt3 = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='" + textBox2.Text + "'WITH REPLACE;";
        SqlCommand bu3 = new SqlCommand(sqlStmt3, con);
        bu3.ExecuteNonQuery();

        string sqlStmt4 = string.Format("ALTER DATABASE [" + database + "] SET MULTI_USER");
        SqlCommand bu4 = new SqlCommand(sqlStmt4, con);
        bu4.ExecuteNonQuery();

        MessageBox.Show("database restoration done successefully");
        con.Close();

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

Fore more explanation, watch this video: