SMO - Working with databases

Enumerating databases, filegroups, and files
The Database property of the Server object represents a collection of Database objects. Using this collection, you can enumerate the databases on SQL Server.

Server srv = new Server(conn);
foreach (Database db in srv.Databases)
{
    Console.WriteLine(db.Name);
    foreach (FileGroup fg in db.FileGroups)
    {
        Console.WriteLine("   " + fg.Name);
        foreach (DataFile df in fg.Files)
        {
            Console.WriteLine("      " + df.Name + " " + df.FileName);
        }
    }
}

Enumerating database properties
Database properties are represented by the Properties property of a Database object. Properties is a collection of Property objects. The following sample demonstrates how to get database properties:

Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
foreach (Property prop in database.Properties)
{
    Console.WriteLine(prop.Name + " " + prop.Value);
}

Creating databases
With SMO, you can create databases. When you want to create a database, you must create the Database object. This example demonstrates how to create a database named MyNewDatabase and create a data file called MyNewDatabase.mdf (in the primary filegroup) and a log file named MyNewDatabase.log.

Database database = new Database(srv, "MyNewDatabase");
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));
DataFile dtPrimary = new DataFile(database.FileGroups["PRIMARY"], 
         "PriValue", @"E:\Data\MyNewDatabase\MyNewDatabase.mdf");
dtPrimary.Size = 77.0 * 1024.0;
dtPrimary.GrowthType = FileGrowthType.KB;
dtPrimary.Growth = 1.0 * 1024.0;
database.FileGroups["PRIMARY"].Files.Add(dtPrimary);

LogFile logFile = new LogFile(database, "Log", 
        @"E:\Data\MyNewDatabase\MyNewDatabase.ldf");
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
 
database.LogFiles.Add(logFile);
database.Create();
database.Refresh();

SMO allows you to set the Growth of a database and other properties. More about properties can be found on MSDN. When you want to drop a database, just call the Drop() method of the Database object.

Database backup
SMO allows you to backup databases very easily. For backup operations, you must create an instance of the Backup class and then assign the Action property to BackupActionType.Database. Now you have to add a device you want to backup to. In many cases, it is file. You can backup not only to file, but to tape, logical drive, pipe, and virtual device.

Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = database.Name;
backup.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
backup.PercentCompleteNotification = 10;
backup.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
backup.SqlBackup(srv);

SMO allows you to monitor the progress of a backup operation being performed. You can easily implement this feature. The first thing you must do is create an event handler with the PercentCompleteEventArgs parameter. This parameter includes the Percent property that contains the percent complete value. This value is an integer between 0 and 100.

static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
    Console.WriteLine(e.Percent);
}

Performing a log backup operation is similar to a database backup. Just set the Action property to Log instead of Database.

Database restore
SMO allows you to perform database restore easily. A database restore operation is performed by the Restore class which is in the Microsoft.SqlServer.Management.Smo.Restore namespace. Before running any restore, you must provide a database name and a valid backup file. Then you must set the Action property. To restore a database, set it to RestoreActionType.Database. To restore a log, just set it to RestoreActionType.Log. During restore, you can monitor the progress of the restoring operation. This could be done the same way as in the case of database backup.

Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
restore.Database = database.Name;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
restore.SqlRestore(srv);

New Article on CodeProject.com

Before couple of days I have posted a new article on CodeProject.com. In this article I will show you, how to backup and restore databases using SMO. More about that you can find here: http://www.codeproject.com/KB/database/SQLServer_Backup_SMO.aspx

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);
        }
    }
}