Performing Database Backup by SMO

By : Kasim Wirama, MCITP, MCDBA

This series of SMO posting, I would like to show how to do database backup with SMO. Backup operation is represented by Microsoft.SqlServer.Management.Smo.Backup class. The example I show here is how to use the SMO backup class by doing full AdventureWorks2008 database backup on my local SQL Server Machine, so let’s get started by creating windows form application in Visual Studio 2008.

Add 4 references : Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended. Double click on Form1 to switch into code behind page and put 2 namespace declaration here :

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

Now, create 1 button, 1 label, and 1 progress bar control on the form, and create click event handler of the button and put code here :

progressBar1.value = 0;
progressBar1.Maximum = 100;

Backup bkup = new Backup();bkup.Action = BackupActionType.Database;
bkup.Database = “AdventureWorks2008”;
bkup.Incremental = false;
bkup.Devices.Add (new BackupDeviceItem(@”C:\AdventureWorks2008.bak”, DeviceType.File));
bkup.Initialize = true;
bkup.PercentCompleteNotification = 10;
bkup.PercentComplete += new PercentCompleteEventHandler(backup_PercentComplete);Server srv = new Server(“(local)”);
bkup.SqlBackup (srv); Let’s go through code steps above. Create instance of SMO Backup class. Define backup type (database/log/filegroup backup). Specify database you would like to backup (for example AdventureWorks2008).

If you would like to run backup as incremental backup, specify value true for Incremental property of the backup instance; otherwise it is false. And specify backup filename by adding new BackupDeviceItem.

If you would like to overwrite existing backup set inside backup file, put value true into Initialize property of the backup instance. And put PercentCompleteNotification to 10; that means that you instruct the backup object to let you know backup progress for each 10% increment. And specify eventhandler for each completion 10% incremental steps.

2 remaining last steps is define SQL Server and run backup by issuing method : SqlBackup on the SQL Server.

You could set backup progress value into label and progress bar control so that user could see backup progress inside the event handler (backup_PercentComplete). In the event handler put the code below :progressBar1.value = e.Percent;
label1.Text = e.Percent.ToString() + “ % complete”; Because the UI got freezed during update progress running before 100%, it’s better to implement threading, here is complete code to implement threading so that user could do any other task on the windows form user interface (UI) whilst waiting backup operation to complete.

    public partial class Backup : Form
    {
        public Backup()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            button1.Enabled = false;
            Thread tr = new Thread(new ThreadStart(doBackup));
            tr.Start();
        }

        private void doBackup()
        {
            progressBar1.Value = 0;
            progressBar1.Maximum = 100;

smo.Server srv = new smo.Server("(local)");
smo.Backup backup = new smo.Backup();

            backup.Action = Microsoft.SqlServer.Management.Smo.BackupActionType.Database;
            backup.Database = "AdventureWorks2008";
            backup.Incremental = false;
            backup.Devices.Add(new Microsoft.SqlServer.Management.Smo.BackupDeviceItem(@"C:\adventureWorks2008.bak", smo.DeviceType.File));
            backup.Initialize = true;
            backup.PercentCompleteNotification = 10;
            backup.PercentComplete += new Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler(backup_PercentComplete);

backup.SqlBackup(srv);
}

        void backup_PercentComplete(object sender, Microsoft.SqlServer.Management.Smo.PercentCompleteEventArgs e)
        {
            this.Invoke(new displayProgress_delegate(displayProgress), e.Percent);
        }

private delegate void displayProgress_delegate(int progress);

        private void displayProgress(int progress)
        {
            label1.Text = progress.ToString() + " % complete";
            progressBar1.Value = progress ;

            if (progress == 100)
                button1.Enabled = true;
        }
    }

Here is screenshot as combination of C# threading and SMO backup operation implementation to give non-freezing user interface.