The Problem

I don't really do MS SQL (preferring MySQL and PostgreSQL) so it came as a surprise to me today when I found that MS SQL backup files can contain multiple backup sets (within the "volume" that is one file).

When restoring such files via the MS SQL GUI, the summary information includes a "Position" column to say which is being used and there's a "Timeline" button that lets you change it. The GUI defaults to restoring the most recent backup.

However when restoring via a script (in this case written in C# using the SMO bindings) the default is to use the first backup, ie the oldest. This wasn't what I wanted.

The Solution

It took a lot of reading official documentation and some slightly incomplete SO articles, to eventually arrive at a very small code change:

    // Make sure we're using the latest backup set / file number
    DataTable backupHeaderInfo = rs.ReadBackupHeader(srv);
    rs.FileNumber = Convert.ToInt32(backupHeaderInfo.AsEnumerable().Max(backupInfo => backupInfo["Position"]));
    Console.WriteLine("Found " + backupHeaderInfo.Rows.Count + " backup points within source, restoring FileNumber: " + rs.FileNumber);

Full Example

None of my research showed up a complete, working code example so here's mine, tested and working as of today.

using System;
using System.Data;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

...

    ServerConnection srvConn = new ServerConnection();
    srvConn.Login = ...;
    srvConn.Password = ...;
    Server srv = new Server(srvConn);

...

    string localFilePath = @"C:\Database\temp\source.bak";

    // Define a Restore object variable.   
    Restore rs = new Restore();
    // Set the NoRecovery property to false to take the database out of the restoring state, transactions will be recovered if they exist   
    rs.NoRecovery = false;
    rs.ReplaceDatabase = true;
    // Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.   
    BackupDeviceItem bdi = default(BackupDeviceItem);
    bdi = new BackupDeviceItem(localFilePath, DeviceType.File);
    // Add the device that contains the full database backup to the Restore object.   
    rs.Devices.Add(bdi);
    // The original database contains a path to where its data files and log files should be restored, however this path may not
    // exist on this server, so each database which is recovered must have all backup artifact (data and logs, normally) paths
    // altered to point at the relevant folders on this machine.
    // Read the files list for the backup
    DataTable dt = rs.ReadFileList(srv);
    // for every row in the filelist (which is a file to be restored) set up a RelocateFiles entry in the Restore object
    foreach (DataRow r in dt.Rows) 
    {
        string logicalFilename = r.ItemArray[dt.Columns["LogicalName"].Ordinal].ToString();
        string physicalFilename;
        switch (r.ItemArray[dt.Columns["Type"].Ordinal].ToString())
        {
            case "D":   // Data file (mdf)
                physicalFilename = srv.Settings.DefaultFile + db_name + "_" + Path.GetFileName(r.ItemArray[dt.Columns["PhysicalName"].Ordinal].ToString());
                break;      
            case "L":   // Log file (ldf)
                physicalFilename = srv.Settings.DefaultLog + db_name + "_" + Path.GetFileName(r.ItemArray[dt.Columns["PhysicalName"].Ordinal].ToString());
                break;      
            default:    // unexpected type (place in Data folder) but log a warning
                physicalFilename = srv.Settings.DefaultFile + db_name + "_" + Path.GetFileName(r.ItemArray[dt.Columns["PhysicalName"].Ordinal].ToString());
                Console.WriteLine("WARNING - Unexpected file type found in database backup file!");
                Console.WriteLine("WARNING - Type = " + r.ItemArray[dt.Columns["Type"].Ordinal].ToString());
                Console.WriteLine("WARNING - Logical Name = " + r.ItemArray[dt.Columns["LogicalName"].Ordinal].ToString());
                Console.WriteLine("WARNING - Physical Name = " + r.ItemArray[dt.Columns["PhysicalName"].Ordinal].ToString());
                break;
        }
        rs.RelocateFiles.Add(new RelocateFile(logicalFilename, physicalFilename));
        Console.WriteLine("'" + logicalFilename + "' is being restored to '" + physicalFilename + "' was originally '" + r.ItemArray[dt.Columns["PhysicalName"].Ordinal].ToString() + "'");
    }
    // Make sure we're using the latest backup set / file number 
    DataTable backupHeaders = rs.ReadBackupHeader(srv);
    rs.FileNumber = Convert.ToInt32(backupHeaders.AsEnumerable().Max(backupInfo => backupInfo["Position"]));
    Console.WriteLine("Found " + backupHeaders.Rows.Count + " backup points within source, restoring FileNumber: " + rs.FileNumber);
    // Specify the database name.   
    rs.Database = db_name;
    // Restore the full database backup with no recovery.
    rs.SqlRestore(srv);
    // Log the Full Database Restore completion.   
    Console.WriteLine("Database restore of database '" + restoreMessage.db_name + "' completed.");

This example was written with help from the talented Terzo Digital.