Take Backup of a DataBase By Coding

In this article, I am going to show how we can show all Data Server in a network and on selecting a Data Server, how to show all DataBase of that selected Data Server and how to take backup of selected database on a particular location by code.

Show all DataServer, Show all database of a selected DataServer and take backup of a selected database.

These are the reference list..

DbBackup4.JPG

Image 1.

This is my c# code
  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.ComponentModel;    
  4. using System.Data;    
  5. using System.Drawing;    
  6. using System.Linq;    
  7. using System.Text;    
  8. using System.Windows.Forms;    
  9. using Microsoft.SqlServer.Server;    
  10. using Microsoft.SqlServer.Management.Smo;    
  11. using Microsoft.SqlServer.Management;    
  12. using System.IO;    
  13. namespace WindowsFormsApplication1    
  14. {    
  15.     public partial class Form1 : Form    
  16.     {    
  17.         public Form1()    
  18.         {    
  19.             InitializeComponent();    
  20.         }    
  21.         private void Form1_Load(object sender, EventArgs e)    
  22.         {    
  23.             GetAllServer();    
  24.         }    
  25.         //To Get All Server    
  26.         private void GetAllServer()    
  27.         {    
  28.             DataTable dt = SmoApplication.EnumAvailableSqlServers(false);    
  29.             if (dt.Rows.Count > 0)    
  30.             {    
  31.                 foreach (DataRow dr in dt.Rows)    
  32.                 {    
  33.                     comboBoxServer.Items.Add((dr["Name"]));    
  34.                 }    
  35.             }    
  36.         }    
  37.         //To Get All DataBase of a Selected Server    
  38.         private void GetAllDataBase()    
  39.         {    
  40.             System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=" + comboBoxServer.SelectedItem.ToString() + ";uid=" + textBoxUid.Text + ";pwd=" + textBoxPassword.Text + ";");    
  41.             SqlCon.Open();    
  42.             System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();    
  43.             SqlCom.Connection = SqlCon;    
  44.             SqlCom.CommandType = CommandType.StoredProcedure;    
  45.             SqlCom.CommandText = "sp_databases";    
  46.             System.Data.SqlClient.SqlDataReader SqlDR;    
  47.             SqlDR = SqlCom.ExecuteReader();    
  48.             while (SqlDR.Read())    
  49.             {    
  50.                 comboBoxDatabaseList.Items.Add(SqlDR.GetString(0));    
  51.             }    
  52.         }    
  53.         //To Take Backup    
  54.         private void TakeBackUp(string BackupDBName, string FileNamePath)    
  55.         {    
  56.             try    
  57.             {    
  58.                 if (textBoxUid.Text != "" && textBoxPassword.Text != "")    
  59.                 {    
  60.                     Server sqlServerInstance = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(new System.Data.SqlClient.SqlConnection("Data Source=" + comboBoxServer.SelectedItem.ToString() + ";Initial Catalog=" + comboBoxDatabaseList.SelectedItem.ToString() + "; uid=" + textBoxUid.Text + "; pwd=" + textBoxPassword.Text + ";")));    
  61.                     Backup objBackup = new Backup();    
  62.                     objBackup.Devices.AddDevice(FileNamePath, DeviceType.File);    
  63.                     objBackup.Database = BackupDBName;    
  64.                     objBackup.Action = BackupActionType.Database;    
  65.                     objBackup.SqlBackup(sqlServerInstance);    
  66.                     MessageBox.Show("The backup of database " + "'" + BackupDBName + "'" + " completed sccessfully""Microsoft SQL Server Management Studio", MessageBoxButtons.OK, MessageBoxIcon.Information);    
  67.                 }    
  68.                 else    
  69.                 {    
  70.                     MessageBox.Show("Please enter userId and password");    
  71.                 }    
  72.             }    
  73.             catch (Exception ex)    
  74.             {    
  75.                 MessageBox.Show(ex.Message);    
  76.             }    
  77.         }    
  78.         private void button1_Click(object sender, EventArgs e)    
  79.         {    
  80.             if (comboBoxDatabaseList.SelectedIndex > 0)    
  81.             {    
  82.                 if (textBoxBackFileName.Text != "")    
  83.                 {    
  84.                     TakeBackUp(comboBoxDatabaseList.SelectedItem.ToString(), "D:\\" + textBoxBackFileName.Text + ".bak");    
  85.                 }    
  86.                 else    
  87.                 {    
  88.                     MessageBox.Show("Please type a name for backup file");    
  89.                 }    
  90.     
  91.             }    
  92.             else    
  93.             {    
  94.                 MessageBox.Show("Please select a database to backup");    
  95.             }    
  96.         }    
  97.         private void button2_Click(object sender, EventArgs e)    
  98.         {    
  99.             this.Dispose();    
  100.         }    
  101.         private void comboBoxServer_SelectedIndexChanged(object sender, EventArgs e)    
  102.         {    
  103.             comboBoxDatabaseList.Items.Clear();    
  104.             textBoxUid.Text = "";    
  105.             textBoxPassword.Text = "";    
  106.             MessageBox.Show("Enter UserId And Password to connect this DataSource.");    
  107.         }    
  108.         private void buttonConnectDb_Click(object sender, EventArgs e)    
  109.         {    
  110.             if (comboBoxServer.SelectedIndex > 0)    
  111.             {    
  112.                 if (textBoxUid.Text != "" && textBoxPassword.Text != "")    
  113.                 {    
  114.                     GetAllDataBase();    
  115.                     MessageBox.Show("Successfully Connected.");    
  116.                 }    
  117.                 else    
  118.                 {    
  119.                     MessageBox.Show("Please enter userId and password");    
  120.                 }    
  121.             }    
  122.             else    
  123.             {    
  124.                 MessageBox.Show("Please select a Server to connect");    
  125.             }    
  126.         }    
  127.     }    
  128. }  
When running the application

DbBackup1.JPG 

Image 2.

After successfully connecting select database and type the name of backup file this backup file will save in D Drive(You can change the location).

DbBackup2.JPG

Image 3.

DbBackup3.JPG

Image 4.