Saturday, October 30, 2010

SQL Server - Database backup using SMO


In order to perform a backup using SMO we require two objects, a Server object and a Backup object. In its simplest form, a Backup object requires only a few properties to be set before calling the SqlBackup method and passing in the Server object as can be seen in the following example that does a Full database backup of the SMO database to the file c:\SMOTest.bak

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Backup bkp = new Backup();
            bkp.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
            bkp.Database = "SMO";
            bkp.Action = BackupActionType.Database;
            bkp.Initialize = true;
            bkp.PercentCompleteNotification = 10;
            bkp.PercentComplete += new PercentCompleteEventHandler(bkp_PercentComplete);
            bkp.SqlBackup(svr);
        }

        static void bkp_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            Console.WriteLine(e.Percent.ToString() + "% backed up");
        }
    }
}

There are many other properties that can be set for the Backup object. For the full list see the Backup Object in Books On Line (if you have SQL2005 Books On Line installed click this link to be taken to the correct page).

Filegroup Backups

In the example below, just the WorkOrderGroup filegroup of the Adventureworks database is backed up (the script to convert the AdventureWorks OLTP demo database into a multi filegroup database can be found in the SQL Server Engine samples and by default is located in C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\FileGroups\Scripts). Filegroups to be backed up are specified by adding them to the Backup objects DatabaseFileGroups collection. In order to backup specific files, they can be added to the DatabaseFiles collection.

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Backup bkp = new Backup();
            bkp.Database = "AdventureWorks";
            bkp.Action = BackupActionType.Files;
            bkp.DatabaseFileGroups.Add("WorkOrderGroup");
            bkp.Devices.AddDevice(@"C:\AWFGTest.bak", DeviceType.File);
            bkp.SqlBackup(svr);
        }
    }
}

Differential Backups

Performing a differential backup is very straightforward, simply set the Incremental property of the Backup object to True

Log Backups

Log backups are equally straightforward, simply set the Action property of the Backup object to BackupActionType.Log

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Backup bkp = new Backup();
            bkp.Action = BackupActionType.Log;
            bkp.Database = "SMO";
            bkp.Devices.AddDevice(@"c:\SMOTest.trn", DeviceType.File);
            bkp.SqlBackup(svr);
        }
    }
}

2 comments:

  1. This post is great. Thank you for this post. I like this type of people who share knowledge with others.seo company India

    ReplyDelete
  2. Hi sem,
    I am very glad you like this post and it is always good to see comments from people who consider my work as valuable and helpful.

    Thank You
    Robert

    ReplyDelete