title: Using SMO Restore Class when there are multiple backup points in .bak file
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I have already tried to set the Restore.ToPointInTime but that won't work due to the recovery model of the database being simple.</p>
  
  <p>I have found a MSDN article describing how to choose your restore time, and it includes setting the database to full recovery mode:</p>
  
  <p><a href="http://technet.microsoft.com/en-us/library/ms179451(v=sql.105).aspx" rel="noreferrer">http://technet.microsoft.com/en-us/library/ms179451(v=sql.105).aspx</a></p>
  
  <p>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.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Here is the code I am using, including a recent change which attempts to set the database recovery model via smo:</p>
  
  <pre><code>    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 =&gt; backupInfo.BackupStartDate);
  
          restore.ToPointInTime = latestBackupDate.ToString();
  
          server.KillAllProcesses(databaseName);
  
          restore.SqlRestore(server);
      }
  </code></pre>
  
  <p>It seems like this should do the trick, however the line </p>
  
  <pre><code>targetDatabase.RecoveryModel = RecoveryModel.Full
  </code></pre>
  
  <p>does not seem to do anything to change the recovery model, leading me to still get the following exception:</p>
  
  <blockquote>
    <p>The STOPAT option is not supported for databases that use the SIMPLE recovery model.
    RESTORE DATABASE is terminating abnormally.</p>
  </blockquote>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>I added the line</p>
  
  <pre><code>targetDatabase.Alter();
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>EDIT 3:</strong></p>
  
  <p>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.</p>
  
  <p>The working code is below.</p>
  
  <pre><code>    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 =&gt; backupInfo.BackupType == BackupType.Database).Max(backupInfo =&gt; backupInfo.Position);
  
          server.KillAllProcesses(databaseName);
  
          restore.SqlRestore(server);
  
          targetDatabase.SetOnline();
      }
  </code></pre>