SQL Server Database Backup and Restore in C#

• ​
  • 
    

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. 1. 
      
2. ​​Microsoft.SqlServer.Smo​​
3. ​​Microsoft.SqlServer.SmoExtended​​
4. ​​Microsoft.SqlServer.ConnectionInfo​​
5. ​​Microsoft.SqlServer.Management.Sdk.Sfc​​
   1. 
      

After Adding References, you need to add 2 ​​using​​ statements:

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

Using 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();

            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:

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

Database Backup

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

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.