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