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 restore using SMO

In this short article I'will show you how to use SMO (Server Management Object) to do restore of the database. In order to perform a restore using SMO we require two objects, a Server object and a Restore object. In its simplest form, a Restore object requires only a few properties to be set before calling the SqlRestore method and passing in the Server object as can be seen in the following example that does a Full database restore of the SMO database from the file c:\SMOTest.bak, replacing the database it it already exists. This example also uses an Event Handler for the PercentComplete event to display the restore progress.

using System;
using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
 class Program
 {
  static void Main()
  {
   Server svr = new Server();
   Restore res = new Restore();
   res.Database = "SMO";
   res.Action = RestoreActionType.Database;
   res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
   res.PercentCompleteNotification = 10;
   res.ReplaceDatabase = true;
   res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
   res.SqlRestore(svr);
  }
  
  static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
  {
   Console.WriteLine(e.Percent.ToString() + "% restored");
  }
    }
}

Restoring a Database to a New Location

Using SMO, the equivalent of the T-SQL WITH MOVE syntax for restores is to use the RelocateFiles property of the Restore Object and the RelocateFile object. In the example below, we will restore a copy of the SMO database to a database called SMO2 with the data and log files on the C: drive. The RelocateFile constructor can take two parameters, the first is the logical filename and the second is the physical filename. This provides the mapping of where to move the files during the restore.

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Restore res = new Restore();
            res.Database = "SMO2";
            res.Action = RestoreActionType.Database;
            res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
            res.ReplaceDatabase = true;

            res.RelocateFiles.Add(new RelocateFile("SMO", @"c:\SMO2.mdf"));
            res.RelocateFiles.Add(new RelocateFile("SMO_Log", @"c:\SMO2.ldf"));

            res.SqlRestore(svr);
        }
    }
}

Reading Backup File Information

There are a number of methods of the Restore object that can be used to obtain information about a backup device and the files it contains including ReadBackupHeader, ReadFileList and ReadMediaHeader. In the example below, we will use the ReadFileList method to obtain the list of Logical filenames on the disk device c:\SMOTest.bak and display them on the console.

using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Restore res = new Restore();
            DataTable dt;
            DataRow[] foundrows;

            res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
            dt = res.ReadFileList(svr);

            foundrows = dt.Select();

            foreach (DataRow r in foundrows)
            {
                Console.WriteLine(r["LogicalName"].ToString());
            }
        }
    }
}

Log Restoresjavascript:void(0)

Log restores are equally straightforward, simply set the Action property of the Restore object to RestoreActionType.Log. Additional properties can be set for Log backups such as ToPointInTime which allows recovery to a specific point in time.

using System;
using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Restore res = new Restore();
            res.Database = "SMO";
            res.Action = RestoreActionType.Log;
            res.Devices.AddDevice(@"C:\SMOTest.trn", DeviceType.File);
            res.NoRecovery = false;
            res.SqlRestore(svr);
        }
    }
}

SQL Server - Restore Database and difference between RECOVERY and NORECOVERY

When I was preparing myself for exam 70-432 I found, that one objective was about database restoring. After study I had one question I couldn't answer. The question was: what is the difference between RECOVERY and NORECOVERY? After a short googling I found some definitions. On MSDN I found this:

Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.

RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.

Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

But in books online I think it is much more better explained:

While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep database in state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create itegrity issues. Once all backup file is restored run RESTORE command with RECOVERY option to get database online and operational.

It is also important to be acquainted with the restore sequence of how full database backup is restored.

First, restore full database backup, differential database backup and all transactional log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option.

Following is the sample Restore Sequence
RESTORE DATABASE mydbname FROM full_database_backup WITH NORECOVERY;

RESTORE DATABASE mydbname FROM differential_backup WITH NORECOVERY;

RESTORE LOG mydbname FROM log_backup WITH NORECOVERY;

-- Repeat this till you restore last log backup

RESTORE DATABASE mydbname WITH RECOVERY;