Using SQL SMO to Backup/Restore your SQL Server 2008 DB in .NET 4.0

             Add References From VS 2010 Project to:

    i) Microsoft.SqlServer.ConnectionInfo.dll

ii) Microsoft.SqlServer.Management.Sdk.Sfc.dll

iii) Microsoft.SqlServer.Smo.dll

iv) Microsoft.SqlServer.SqlEnum.dll

v) Microsoft.SqlServer.SmoExtended.dll

I have SQL Server 2008 Express installed on my machine and the above DLLs are located in:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

    Once the references are added, the last step is to:

Add these using directives in your code

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Example:

Below are screenshots from a Windows Forms 4.0 application that enables users to Backup/Restore a database using SQL Server 2008 SMO.


Backing up the Database

Fig. 1 Initially the app shows the Server, the Database and all other databases installed on this Server

 Fig. 2 To Backup the database (in this case my DB is called ShopOrders) clicking the "Backup..." button shows a Folder Browser Dialog wich enables you to choose the location where you want your DB backed up.

 Fig. 3 The user creates a new folder on the C:\ drive where the .bak file will be saved

Fig. 4 The database backup is complete

Fig. 5 The backed up database file ShopOrders.bak



Restoring the Database

Fig. 6 To restore this DB just select the .bak file from the backup location.


The .cs of the Form shown above:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ShopOrders

{
    public partial class AdminDB : Form
    {
        Server server;
        Database database;
        ServerConnection serverConnection;

        public AdminDB()

        {
            InitializeComponent();
        }

        //****************************************************

        private void AdminDB_Load(object sender, EventArgs e)

        {
            this.WindowState = FormWindowState.Maximized;
            GetServerDbData();

        }

        //****************************

        private void GetServerDbData()
        {
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);

            this.serverConnection = new ServerConnection(sqlConnection);

            this.server = new Server(serverConnection);
            this.database = new Database(this.server, "ShopOrders");

            this.lblServerName.Text = this.server.Name;

            this.lblDatabaseName.Text = this.database.Name;

            foreach (Database db in this.server.Databases)

            {
                this.listBoxDatabases.Items.Add(db.Name);
            }
        }

        //******************************************************

        private void btnBackup_Click(object sender, EventArgs e)
        {
            this.folderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer;

            if (this.folderBrowserDialog.ShowDialog() == DialogResult.OK)

            {

                try

                {
                    this.Cursor = Cursors.WaitCursor;
                    Backup backup = new Backup();
                    this.serverConnection.Connect();

                    backup.Devices.AddDevice(this.folderBrowserDialog.SelectedPath + @"\" + this.database.Name + ".bak", DeviceType.File);

                    backup.Database = this.database.Name;

                    backup.Action = BackupActionType.Database;

                    backup.Initialize = true;

                    backup.PercentCompleteNotification = 5;

                    backup.PercentComplete += new PercentCompleteEventHandler(bkp_PercentComplete);

                    backup.SqlBackup(this.server);

                    this.serverConnection.Disconnect();

                    backup = null;

                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    this.Cursor = Cursors.Default;
                }
            }
        }

        //*************************************************************************

        private void bkp_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            this.lblPercentageComplete.Text = "Backup Completed " + e.Percent.ToString() + "%";
        }

        //******************************************************

        private void btnRestore_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    this.Cursor = Cursors.WaitCursor;

                    Restore restore = new Restore();

                    this.serverConnection.Connect();

                    restore.Devices.AddDevice(this.openFileDialog.FileName, DeviceType.File);

                    this.server.DetachDatabase("ShopOrders", true);
                    restore.Database = "ShopOrders";

                    restore.Action = RestoreActionType.Database;

                    restore.PercentCompleteNotification = 10;
                    restore.ReplaceDatabase = true;
                    restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);
                    restore.SqlRestore(this.server);
                    this.serverConnection.Disconnect();
                    restore = null;
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    this.Cursor = Cursors.Default;
                }
            }
        }

        //*************************************************************************

        private void restore_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            this.lblRestorePercentageComplete.Text = "DB restored " + e.Percent.ToString() + "%";
        }
    }
}


NOTE: The name of the database to be backed up/restored is hard coded in the app. You can easily modify it, to enable the user to select the desired database from the ListBox.