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