Omarsoft For IT Solutions (Java Codes ,C#.NET Codes , ASP.NET Codes ,VB.NET Codes ,Oracle Database Administration, Real Application Cluster , Remote Support, Cloud Services , Networks ,Virtualization....
  • الأنظــمــة المكتبية        Windows App Programming
  • أنظــمـةالويــب        Web based systems programming
  • تطبيقات الهواتف الذكية     smartphones programming
  • إدارة قواعــــــد البيــــــــــــــــانات        Database Administration
  • إدارة الشبكـــــــــــــــــــــــــــــــــات        Networks Administration
  • إدارة الســـيــرفرات (ويب - محلية)  Servers Administration
  • إدارة مخـــــــــــــــــازن البيــــــــــــانات     Storage Administration
  •             N Computing & 2X Application services

    Social Icons

Loading...

SQL Server Database Backup and Restore in C# .NET



Before coding, you must set the reference to the SMO assembly. You need to add these components:
  1. Microsoft.SqlServer.Smo
  2. Microsoft.SqlServer.SmoExtended
  3. Microsoft.SqlServer.ConnectionInfo
  4. Microsoft.SqlServer.Management.Sdk.Sfc
After Adding References, you need to add 2 using statements:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

The Code

The following code creates connection with SQL Server. To execute:
"select * from sys.databases"
The above query retrieves all databases from SQL Server.
public void Createconnection()
{
DBbackup.DataBaseClass dbc = new DataBaseClass();
cbdatabasename.Items.Clear();
// select * from sys.databases getting all database name from sql server
cmd = new OdbcCommand("select * from sys.databases", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbdatabasename.Items.Add(dr[0].ToString());
}
dr.Close();
}
The following code gets server names that exist. To execute:
"select *  from sys.servers"
The above query retrieves servers:
public void serverName()
{
DBbackup.DataBaseClass dbc = new DataBaseClass();
// select * from sys.servers getting server names that exist
cmd = new OdbcCommand("select * from sys.servers", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbservername.Items.Add(dr[1].ToString());
}
dr.Close();
}

Database Backup

public void backup(string str)
{
if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
{
MessageBox.Show("Server Name & Database can not be Blank");
return;
}
else
{
if (str == "backup")
{
saveFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
// the below query get backup of database you specified in combobox
query("Backup database " + cbdatabasename.Text +
" to disk='" + saveFileDiaog1.FileName + "'");
MessageBox.Show("Database BackUp has been created successfully.");
}
}
}
}

Database Restore

public void Restore(OdbcConnection sqlcon, string DatabaseFullPath, string backUpPath)
{
using (sqlcon)
{
string UseMaster = "USE master";
OdbcCommand UseMasterCommand = new OdbcCommand(UseMaster, sqlcon);
UseMasterCommand.ExecuteNonQuery();
// The below query will rollback any transaction which is running on database
string Alter1 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate";
OdbcCommand Alter1Cmd = new OdbcCommand(Alter1, sqlcon);
Alter1Cmd.ExecuteNonQuery();
// The below query will restore database file from disk where backup was //taken ....
string Restore = @"RESTORE DATABASE
[" + DatabaseFullPath + "] FROM DISK = N'" +
backUpPath + @"' WITH FILE = 1, NOUNLOAD, STATS = 10";
OdbcCommand RestoreCmd = new OdbcCommand(Restore, sqlcon);
RestoreCmd.ExecuteNonQuery();
// the below query change the database back to multiuser
string Alter2 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Multi_User";
OdbcCommand Alter2Cmd = new OdbcCommand(Alter2, sqlcon);
Alter2Cmd.ExecuteNonQuery();
Cursor.Current = Cursors.Default;
}
}

Using of Code:

create new project (C#) :

  1. --- In Designer Mode:

Add new form --> add 2 Combobox  (cbservername , cbdatabasename).
Add 2 buttons (backupbtn , restorebtn) 

     2.--- In the Code Behind of form :

- in form_load event:
 private void Form1_Load(object sender, EventArgs e)
        {
                       
            serverName();
        }

- in cbservername_SelectedIndexChanged event write:
private void cbservername_SelectedIndexChanged(object sender, EventArgs e)
        {
            Createconnection();
        }
       
- in backupbtn_click event:
 private void backupbtn_Click(object sender, EventArgs e)
        {
            backup("backup");
        }
- in restorebtn_click event:
private void restorebtn_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
            {
                MessageBox.Show("Server Name & Database can not be Blank", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {

                openFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";

                DBbackup.DataBaseClass dbc = new DataBaseClass();
                Restore(dbc.openconn(), cbdatabasename.SelectedItem.ToString(), openFileDialog1.FileName);
               MessageBox.Show("Database Backup file has been restore successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
        }

Sana'a Yemen - 50th st.

+967 738166685

omar.soft2000@gmail.com

للاتصال بنا CONTACT US

الاسم Name

بريد إلكتروني Email *

رسالة Message *

2015-2023 © All Rights Reserved Omarsoft
Back To Top