Click here to Skip to main content
15,393,863 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick
Posted 7 Feb 2015

Stats

155.1K views
16.4K downloads
29 bookmarked

SQL Server Database Backup and Restore in C#

Rate me:
Please Sign up or sign in to vote.
3.34/5 (29 votes)
7 Feb 2015CPOL1 min read
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Syed Noman Ali Shah
Software Developer raideIT Software Solution
Pakistan Pakistan
No Biography provided

Comments and Discussions

 
SuggestionNeed comment here Pin
Sanket Shembekar2-Jan-18 23:17
MemberSanket Shembekar2-Jan-18 23:17 
QuestionSMO Usage ? Pin
xfarzad22-Oct-16 0:09
Memberxfarzad22-Oct-16 0:09 
Generalfound help full. Pin
Meer Wajeed Ali2-Jun-16 20:40
professionalMeer Wajeed Ali2-Jun-16 20:40 
QuestionDownload Link Broken !! Pin
Member 1247315720-Apr-16 2:27
MemberMember 1247315720-Apr-16 2:27 
QuestionClarification in not enough Pin
Md. Marufuzzaman15-Feb-15 2:04
professionalMd. Marufuzzaman15-Feb-15 2:04 
GeneralMy vote of 2 Pin
dmjm-h9-Feb-15 17:17
Memberdmjm-h9-Feb-15 17:17 
GeneralMy vote of 2 Pin
Master689-Feb-15 2:22
MemberMaster689-Feb-15 2:22 
GeneralRe: My vote of 2 Pin
Syed Noman Ali Shah9-Feb-15 22:42
professionalSyed Noman Ali Shah9-Feb-15 22:42 
GeneralRe: My vote of 2 Pin
Syed Noman Ali Shah25-Mar-15 2:55
professionalSyed Noman Ali Shah25-Mar-15 2:55 
GeneralRe: My vote of 2 Pin
andrewtheart6-Mar-17 6:04
Memberandrewtheart6-Mar-17 6:04 
Answerdatabase backup using SMO Pin
50minutos7-Feb-15 5:35
Member50minutos7-Feb-15 5:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.