Using SMO Restore Class with Multiple Backup Points in a .bak File

Question

I am trying to use SMO to write a simple database backup / restore utility. This works very well when there is only one point in time in the backup file. However, when there is a backup file with several backup points (not backup sets), SMO always selects the earliest, while SSMS will always select the latter.

This leads to incorrect data recovery, and I would like to find out if there is a property that I can set that will force the Restore class to always use the last backup point.

I already tried installing Restore.ToPointInTime, but this will not work due to a simple database recovery model.

I found an MSDN article that describes how to choose a recovery time and includes setting up the database for full recovery mode:

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

This is necessary when using SMO, and is there a way to do this using pure SMO (without C # sql commands)? I used Restore.ReadBackupHeaders, and from this I can extract the available backup points in time, but could not install the one that needs to be restored anywhere.

EDIT:

Here is the code I'm using, including a recent change that is trying to install a 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); } 

This seems to do the trick, however the line

 targetDatabase.RecoveryModel = RecoveryModel.Full 

doesn't seem to do anything to change the recovery model, as a result of which I still get the following exception:

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

EDIT 2:

I added a line

 targetDatabase.Alter(); 

and he fixed the problem of not updating. However, it is now being restored, but it leaves the database in recovery mode, so it cannot be queried.

EDIT 3:

I got the code working by setting the Restore.FileNumber property to the maximum position value in BackupHeaders, which seems to do the trick, although I'm still not sure why there are several backup headers in the backup file, but only one set of backups.

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 the fact that you say that you do not have multiple sets of backups, I think so. In the documentation for the backupset table:

The backup set contains a backup from one successful backup operation.

So, if you have several restore points in one backup file, you have several sets of backups. Check this by querying the dbo.backupset table in msdb

Pedantry aside, I think you're looking for the FileNumber property of a Restore object. This corresponds to the FILE = n backup set option in the T-SQL recovery command. To get the last, just pull the last line from your ReadBackupHeaders request.

To test yourself, go through the recovery movements through SSMS, and then instead of hitting “ok,” click the “Script” button at the top. I suspect you will see there FILE = <some number> .