Using SMO Restore Class when there are multiple backup points in .bak file

Question

I am attempting to use SMO to write a simple utility to backup/restore databases. This works very well when there is only one point in time in the backup file. However when there is a backup file that has multiple backup points defined (not backup sets) SMO always chooses the earliest, whereas SSMS will always choose the latest.

This leads to an incorrect restore of the data, and I would like to figure out if there is a property that I can set that will force the Restore class to always use the latest backup point.

I have already tried to set the Restore.ToPointInTime but that won't work due to the recovery model of the database being simple.

I have found a MSDN article describing how to choose your restore time, and it includes setting the database to full recovery mode:

http://technet.microsoft.com/en-us/library/ms179451(v=sql.105).aspx

Is this necessary when using SMO, and is there a way to do it using pure SMO (no C# sql commands)? I have used the Restore.ReadBackupHeaders and from that I am able to extract the available backup points in time, but not able to set the one to restore anywhere.

EDIT:

Here is the code I am using, including a recent change which attempts to set the database recovery model via smo:

    public void RestoreDatabase(string databaseName, string backupPath)
    {
        var server = new Server(GetServerConnection());

        //If the database doesn't exist, create it so that we have something
        //to overwrite.
        if (!server.Databases.Contains(databaseName))
        {
            var database = new Database(server, databaseName);
            database.Create();
        }

        var targetDatabase = server.Databases[databaseName];
        targetDatabase.RecoveryModel = RecoveryModel.Full;
        targetDatabase.Alter();

        Restore restore = new Restore();

        var backupDeviceItem = new BackupDeviceItem(backupPath, DeviceType.File);
        restore.Devices.Add(backupDeviceItem);
        restore.Database = databaseName;
        restore.ReplaceDatabase = true;
        restore.Action = RestoreActionType.Database;

        var fileList = restore.ReadFileList(server);

        var dataFile = new RelocateFile();
        string mdf = fileList.Rows[0][1].ToString();
        dataFile.LogicalFileName = fileList.Rows[0][0].ToString();
        dataFile.PhysicalFileName = server.Databases[databaseName].FileGroups[0].Files[0].FileName;

        var logFile = new RelocateFile();
        string ldf = fileList.Rows[1][1].ToString();
        logFile.LogicalFileName = fileList.Rows[1][0].ToString();
        logFile.PhysicalFileName = server.Databases[databaseName].LogFiles[0].FileName;

        restore.RelocateFiles.Add(dataFile);
        restore.RelocateFiles.Add(logFile);

        var backupHeaderInfo = GetBackupHeaderInformation(restore, server);
        var latestBackupDate = backupHeaderInfo.Max(backupInfo => backupInfo.BackupStartDate);

        restore.ToPointInTime = latestBackupDate.ToString();

        server.KillAllProcesses(databaseName);

        restore.SqlRestore(server);
    }

It seems like this should do the trick, however the line

targetDatabase.RecoveryModel = RecoveryModel.Full

does not seem to do anything to change the recovery model, leading me to still get the following exception:

The STOPAT option is not supported for databases that use the SIMPLE recovery model. RESTORE DATABASE is terminating abnormally.

EDIT 2:

I added the line

targetDatabase.Alter();

and it fixed the not updating problem. However It now restores but leaves the database in restoring mode, so it is unable to be queried.

EDIT 3:

I got the code working by setting the Restore.FileNumber property to be the maximum value of the positions in the BackupHeaders, which seems to do the trick, though I'm still unsure why the backup file has multiple backup headers, but only a single backup set.

The working code is below.

    public void RestoreDatabase(string databaseName, string backupPath)
    {
        var server = new Server(GetServerConnection());

        //If the database doesn't exist, create it so that we have something
        //to overwrite.
        if (!server.Databases.Contains(databaseName))
        {
            var database = new Database(server, databaseName);
            database.Create();
        }

        var targetDatabase = server.Databases[databaseName];
        targetDatabase.RecoveryModel = RecoveryModel.Full;
        targetDatabase.Alter();

        Restore restore = new Restore();

        var backupDeviceItem = new BackupDeviceItem(backupPath, DeviceType.File);
        restore.Devices.Add(backupDeviceItem);
        restore.Database = databaseName;
        restore.ReplaceDatabase = true;
        restore.NoRecovery = false;
        restore.Action = RestoreActionType.Database;

        var fileList = restore.ReadFileList(server);

        var dataFile = new RelocateFile();
        dataFile.LogicalFileName = fileList.Rows[0][0].ToString();
        dataFile.PhysicalFileName = server.Databases[databaseName].FileGroups[0].Files[0].FileName;

        var logFile = new RelocateFile();
        logFile.LogicalFileName = fileList.Rows[1][0].ToString();
        logFile.PhysicalFileName = server.Databases[databaseName].LogFiles[0].FileName;

        restore.RelocateFiles.Add(dataFile);
        restore.RelocateFiles.Add(logFile);

        var backupHeaderInfo = GetBackupHeaderInformation(restore, server);
        restore.FileNumber = backupHeaderInfo.Where(backupInfo => backupInfo.BackupType == BackupType.Database).Max(backupInfo => backupInfo.Position);

        server.KillAllProcesses(databaseName);

        restore.SqlRestore(server);

        targetDatabase.SetOnline();
    }

Answer 1

Despite your saying that you don't have multiple backup sets, I think you do. From the documentation for the backupset table:

A backup set contains the backup from a single, successful backup operation.

So, if you have "multiple restore points" in a single backup file, you have multiple backup sets. Verify this by querying the dbo.backupset table in msdb

Pedantry aside, I think you're looking for the FileNumber property on the Restore object. This corresponds to the FILE = n backup set option in the T-SQL restore command. In order to get the last one, just pull the last row from your ReadBackupHeaders call.

To test for yourself, go through the motions of performing a restore through SSMS and then, instead of hitting "ok", hit the "Script" button near the top. I suspect that you'll see a FILE = <some number> in there somewhere.