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

Vingtetun

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.

Fidler

I thought that this was the case, but I performed a Restore.ReadFileList() against the backup and it only returned 2 entries: the mdf and the ldf. When I select the file in SSMS I only see one result set and when I script the backup (as you suggested) it is set at file 1. – Vingtetun

Then how are you making the assessment that the backup file has multiple restore points? – Fidler

I made the assessment by the multiple rows returned by the Restore.ReadBackupHeader() method. Also, the backups can be seen if you select the "Timeline..." option when selecting your media set in SSMS, next to the box labeled "Restore to...". This shows that the backup files contains multiple backups but only a single backup set. Very confusing. In either case I got it working by setting the Restore.FileNumber = the greatest "position" of the backup headers: backupHeaderInfo.Where(backupInfo => backupInfo.BackupType == BackupType.Database).Max(backupInfo => backupInfo.Position); – Vingtetun

Because I got it working using the Restore.FileNumber property I will mark your answer as correct and post the updated code for anyone else who swings by, but I am still confused as to how a backup file can have multiple backup headers, but only one backup set. – Vingtetun

What are you using as a definition of "backup set"? A single backup file on the file system can contain multiple backup sets. Also, it is my understanding that a restore headeronly from disk… statement will display one row per backup set in the file provided. From everything that you're describing, you have multiple backup sets. – Fidler

imgur.com/a/G4SnS This album shows the options during a restore with the .bak file in question. In it you can see that there is only a single backup set, but there is multiple entries in the time line if you select the "Timeline..." option. These timeline entries are the same entries that are listed in the ReadBackupHeader() method. – Vingtetun

Hmm. Well, iirc, that tool goes through the backupset table in msdb for "what backups are available?" information. Out of curiosity, if you choose the May 20 backup and script out the restore operation, what do you get? My guess is that you'll get a different backup file than you're expecting. – Fidler

Let us continue this discussion in chat. – Vingtetun