Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

SQL Server Database Backup and Restore in C#

0.00/5 (No votes)
7 Feb 2015 1  
How to take SQL server database backup and database restoration in C# using SMO

Image 1

Introduction

Developers need to take backup and restore database. Database backup and restore can help you avert disaster. If you backup your files regularly, you can retrieve your information. By taking database backup and restoration through coding, so this could be done via Server Management Objects. So here, I will describe what is SMO and how it will be used for database backup and restoration.

SQL Server Management Objects (also called SMO) is a .NET library which allows you to access and manage all objects of the Microsoft SQL Server.SMO supports SQL Server 2000, 2005 and 2008, 2012. All functions available in SQL Server Management Studio are available in SMO but SMO includes several more features than Management Studio.

Background

You will have to create DSN for connection.

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:

C#
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Using the Code

The following code creates connection with SQL Server. To execute:

SQL
"select * from sys.databases"

The above query retrieves all databases from SQL Server.

C#
public void Createconnection()
        {
            DBbackup.DataBaseClass dbc = new DataBaseClass();
          
            cbservername.Properties.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.Properties.Items.Add(dr[0]);
            }
            dr.Close(); 
        }

The following code gets server names that exist. To execute:

SQL
"select *  from sys.servers"

The above query retrieves servers:

C#
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.Properties.Items.Add(dr[1]);
        }
        dr.Close();
}

Database Backup

C#
public void blank(string str)
      {
              if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
              {
                  XtraMessageBox.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 + "'");

                          XtraMessageBox.Show("Database BackUp has been created successful.");
                      }
                  }
              }
       }

Database Restore

C#
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 that database and brings SQL Server database in a single user mode.
                   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;
               }
            }

Conclusion

This code uses the SQL Server 2005, 2012 backup/restore facility. The code follows the rules of SQL Server 2005, 2012 while backing up or restoring database.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here