Creating SQL Server Backup File With C#

In this article, I would like to show how to create a SQL Server Backup file in C#. You can create a SQL Server database backup using SQL Server Management Studio and you can also use a Transact-SQL statement. We use a backup database and restore the database when our database becomes corrupted or crashes.

To do it using SQL Server Management Studio:

http://www.c-sharpcorner.com/UploadFile/rohatash/database-backup-with-compression-in-sql-server-2012/

So let's have a look at a practical example of how to create it using C#. The example is developed in Visual Studio 2010. 

In SQL Server

The following Query gives the name of the Database and the server name:

Select *  from sysservers  where srvproduct='SQL Server'

go

Select * from sysdatabases

Output

DatabaseName-and-ServerName-in-SQL-Server.jpg

If you want to create a backup of the database just execute the following Query in SQL Server Management Studio.

Backup database Student to disk='E:/test.bak'

The backup of the student database has been created on the given location.

Output

Backup-command-in-SQL-Server.jpg

In Visual Studio 2010,

The SQL Server query above returns the server name and all database names. Now execute it using C# code. To do that create a Windows Forms application and drag and drop the following control onto the form.

Backup-form-in-Visual-Studio.jpg

C# code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Data.SqlClient;
namespace SQLBackUpApp
{
    public partial class Form1 : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader dr;
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            label3.Visible = false;
            serverName(".");         
        }
        public void serverName(string str)
        {
            con = new SqlConnection("Data Source=" + str + ";Database=Master;data source=.; uid=sa; pwd=Micr0s0ft;");
            con.Open();
            cmd = new SqlCommand("select *  from sysservers  where srvproduct='SQL Server'", con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ComboBoxserverName.Items.Add(dr[2]);
            }
            dr.Close();
        }
        public void Createconnection()
        {
            con = new SqlConnection("Data Source=" + (ComboBoxserverName.Text) + ";Database=Master;data source=.; uid=sa; pwd=Micr0s0ft;");
            con.Open();
            ComboBoxDatabaseName.Items.Clear();
            cmd = new SqlCommand("select * from sysdatabases", con);
            dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                ComboBoxDatabaseName.Items.Add(dr[0]);
            }
            dr.Close();
        }
        public void query(string que)
        {
            // ERROR: Not supported in C#: OnErrorStatement
            cmd = new SqlCommand(que, con);
            cmd.ExecuteNonQuery();
        }
        public void blank(string str)
        {
            if (string.IsNullOrEmpty(ComboBoxserverName.Text) | string.IsNullOrEmpty(ComboBoxDatabaseName.Text))
            {
                // label3.Visible = true;
                MessageBox .Show( "Server Name & Database can not be Blank");
                return;
            }
            else
            {
                if (str == "backup")
                {
                    SaveFileDialog1.FileName = ComboBoxDatabaseName.Text;
                    SaveFileDialog1.ShowDialog();                                     
                    string s = null;
                    s = SaveFileDialog1.FileName;
                    query("Backup database " + ComboBoxDatabaseName.Text + " to disk='" + s + "'");
                    label3.Visible = true;
                    label3.Text = "Database BackUp has been created successful";
                }
            }
        }
        private void cmbbackup_Click(object sender, EventArgs e)
        {
            blank("backup");          
        }
        private void cmbserver_SelectedIndexChanged(object sender, EventArgs e)
        {
            Createconnection();
        }
    }
}

In the code above you can change the connection string corresponding to your database.

Now run the application and select the server name and database name to create the backup.

select-Servername-and-databasename-form-dropdown-in-Visual-Studio.jpg

Now click on the "Backup" Button and select the backup location.

Database-backup-in-Visual-Studio.jpg

Now open the selected location to see the backup file.