User Id :    Password :      New Member   Forgot Password
 
Take Backup of a DataBase By Coding
Description 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.   No. of Views     973
  Rating     0
Author Rahul Saxena   Posted On     02 Oct 2011
Tags SQL SERVER : How To    

Sample Code   Download Code

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

These are the reference list..

Image 1.

This is my c-sharpcode...

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 Microsoft.SqlServer.Server; 
using Microsoft.SqlServer.Management.Smo; 
using Microsoft.SqlServer.Management; 
using System.IO; 
  

namespace WindowsFormsApplication1 
{ 
    public partial class Form1 : Form 
    { 
        public Form1() 
        { 
            InitializeComponent(); 
        }  

        private void Form1_Load(object sender, EventArgs e) 
        { 
            GetAllServer(); 
        } 

        //To Get All Server 

        private void GetAllServer() 
        { 
            DataTable dt = SmoApplication.EnumAvailableSqlServers(false); 
            if (dt.Rows.Count > 0) 
            { 
                foreach (DataRow dr in dt.Rows) 
                { 
                    comboBoxServer.Items.Add((dr["Name"])); 
                } 
            } 
        } 
  

        //To Get All DataBase of a Selected Server 

        private void GetAllDataBase() 
        { 
            System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=" + comboBoxServer.SelectedItem.ToString() + ";uid=" + textBoxUid.Text + ";pwd=" + textBoxPassword.Text + ";"); 
            SqlCon.Open(); 
            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand(); 
            SqlCom.Connection = SqlCon; 
            SqlCom.CommandType = CommandType.StoredProcedure; 
            SqlCom.CommandText = "sp_databases"; 
            System.Data.SqlClient.SqlDataReader SqlDR; 
            SqlDR = SqlCom.ExecuteReader(); 
            while (SqlDR.Read()) 
            { 
                comboBoxDatabaseList.Items.Add(SqlDR.GetString(0)); 
            } 
        }   

        //To Take Backup 

        private void TakeBackUp(string BackupDBName, string FileNamePath) 
        { 
            try 
            { 
                if (textBoxUid.Text != "" && textBoxPassword.Text != "") 
                { 
                    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 + ";"))); 
                    Backup objBackup = new Backup(); 
                    objBackup.Devices.AddDevice(FileNamePath, DeviceType.File); 
                    objBackup.Database = BackupDBName; 
                    objBackup.Action = BackupActionType.Database; 
                    objBackup.SqlBackup(sqlServerInstance); 
                    MessageBox.Show("The backup of database " + "'" + BackupDBName + "'" + " completed sccessfully", "Microsoft SQL Server Management Studio", MessageBoxButtons.OK, MessageBoxIcon.Information); 
                } 
                else 
                { 
                    MessageBox.Show("Please enter userId and password"); 
                } 
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message); 
            } 
        } 

        private void button1_Click(object sender, EventArgs e) 
        { 
            if (comboBoxDatabaseList.SelectedIndex > 0) 
            { 
               if (textBoxBackFileName.Text != "") 
               { 
                    TakeBackUp(comboBoxDatabaseList.SelectedItem.ToString(), "D:\\" + textBoxBackFileName.Text + ".bak"); 
                } 
                else 
                { 
                    MessageBox.Show("Please type a name for backup file"); 
                } 
            } 
            else 
            { 
                MessageBox.Show("Please select a database to backup"); 
            } 
        } 

        private void button2_Click(object sender, EventArgs e) 
        { 
            this.Dispose(); 
        } 

        private void comboBoxServer_SelectedIndexChanged(object sender, EventArgs e) 
        { 
            comboBoxDatabaseList.Items.Clear(); 
            textBoxUid.Text = ""; 
            textBoxPassword.Text = ""; 
            MessageBox.Show("Enter UserId And Password to connect this DataSource."); 
        } 

         private void buttonConnectDb_Click(object sender, EventArgs e) 
       { 
            if (comboBoxServer.SelectedIndex > 0) 
            { 
              if (textBoxUid.Text != "" && textBoxPassword.Text != "") 
                { 
                    GetAllDataBase(); 
                    MessageBox.Show("Successfully Connected."); 
                } 
               else 
                { 
                   MessageBox.Show("Please enter userId and password"); 
               } 
            } 
            else 
            { 
              MessageBox.Show("Please select a Server to connect"); 
           } 
       } 
  } 
}

When run the application

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).



Image 3.

Image 4.

About Author

About Author Rahul shows great interests in working with Microsoft technologies. He specializes in the implementation of DataBase & Graphics. His area of expertise includes: C#, ASP.NET,ADO.NET,Windows Forms & Web Services. He hails from background , Master's in Computer Application. With programming he loves photography, traveling and reading books. Rahul Saxena
No Photo
 
Country India
Company N/A
Home Page N/A

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by