Sql-server – How to recover from a failed SQL Restore

After a failure of the restore code in a webpage the database is left in the "restoring" mode and/or is offline. How do I recover so I have an operational online database – before I exit the code?

Any page load after the failure attempts to check if the user is logged in and requires the database to be on-line and usable – thus the user is no longer logged in. I would prefer to not alter the requirement the user be logged in using the database I am restoring.

Details:

I somehow got a corrupted SQL backup. In this case that is a good thing because it allows me to check my error trapping code. The restore of the Full backup completes but the restore of the following differential backup fails. However my question relates to any failure – not just the one I currently have. FYI the error is: This differential backup cannot be restored because the database has not been restored to the correct earlier state .. I do not need to know the cause – just how to recover after this and after other restore failures.
Any of these options would be OK but I cannot get anything to work after the failure and… before I leave the code.

• abort the current restore and start over with a known good backup – without leaving the code – causing a page load event. I can't seem to abort everything and start over.
• Just put the database back the way it was before the restore started – on line and not in recovering mode. Are there some SMO options I am not aware of to do that?
• Etc. any other options I may be missing?

More details:

• I left out a lot of code to simplify – the working code is attached.
• The code works without error when the backups are good.
• The database is small – 3 M bytes or so.
• The database backups are in Simple mode. We do not need Log Backups/restores or transaction backups/restores
• We do a full backup once a month and differential backups on the same file every day or so. We would never need to restore other than to a selected full or differential backup.

Any suggestions will be very much appreciated – or better yet, point me to some code examples that do the recovery from a restore failure. Most examples I have found do not even trap errors.

Dennis

protected void Restore_Click(object sender, EventArgs e)
{
    string ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
    string RestoreDestinationPath = "C:\\SF_Access\\SciFairMasterWebsite-rev562-InWork\\App_Data\\backup\\SFMASPNETDB_BUSET_Started_2015-10.bak";

    int SelPosNum = 14; // The Postion Number of Differential backup. 21 works 
    int fullBackupPosition = 13; // The Position number of the previous Full Backup 20 works

    string databaseName = "SFMASPNETDB";
    string bufilePath = RestoreDestinationPath ;   
    string dataFilePath = "C:\\SF_Access\\SciFairMasterWebsite-rev562-InWork\\App_Data\\";
    string logFilePath = "C:\\SF_Access\\SciFairMasterWebsite-rev562-InWork\\App_Data\\";

    SqlConnection sqlCon = new SqlConnection(ConnectionString);
    ServerConnection connection = new ServerConnection(sqlCon);

    Server sqlServer = new Server(connection);
    Database db = sqlServer.Databases[databaseName];

    Restore sqlRestore = new Restore();

    BackupDeviceItem deviceItem = new BackupDeviceItem(bufilePath, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;
    sqlRestore.ContinueAfterError = false;  
    sqlRestore.Action = RestoreActionType.Database;

    string dataFileLocation = dataFilePath + databaseName + ".mdf";
    string logFileLocation = logFilePath + databaseName + "_Log.ldf";

    RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
    sqlRestore.ReplaceDatabase = true;
    // WE MUST Take the current database offline to do the restore.
    // Alternate... Some examples just set it to single user then back to multi user after restore

    try
    {
        db.SetOffline(); 
    }
    catch (Exception yx)
    {
        sqlRestore.Abort();   // If set offline fails, This does NOT WORK - leaves the db in restoring status
        connection.Cancel();  
        connection.Disconnect();
        sqlCon.Close();      
        return;
    }

    sqlRestore.NoRecovery = true; // Set this for the Full Backup recovery 
    sqlRestore.Checksum = true;  // Backups made with checksums.
    sqlRestore.FileNumber = fullBackupPosition;    // the previous full backup postition

    try
    {
        sqlRestore.SqlRestore(sqlServer);  // Restore Full Backup with NoRecovery
    }
    catch (Exception ex1)
    {
        sqlRestore.Abort();   // This does not take the database out of "Restoring" status
        db.SetOnline();       // This actually fails so would need a try / catch to handle    
        connection.Cancel();  
        connection.Disconnect();
        sqlCon.Close();    
        return;
    }

    sqlRestore.NoRecovery = false; // now get ready to do the incremental and let it Recover after restore
    sqlRestore.FileNumber = SelPosNum;  // position number of the incremental backup done after the full Backup
    sqlRestore.Checksum = true;

    try
    {
         sqlRestore.SqlRestore(sqlServer);  // Restore incremental with  RESTORE ***THIS FAILS***
    }
    catch (Exception ex)
    {
        // ***   THIS IS THE AREA IN QUESTION ******
        // This code leaves the database in "Restoring" status.  
        //  Since the database is the membership database and administration is logged on, if the database is not
        //  restored, all other pages on the website requiring login fail. 
        // I need to:
        //  Take the database out of 'restoring' and start over with a different backup 
        //    (I could make a Full Backup before I start for that purpose and it would probably restore without error.) 
        //  Put it back online the way it was before I started this code (Delete it and attach a copy I could make before I start)  
        //  or any other way to recover with an 'on line' database - before I exit this page. 
        string msg = ex.Message;
        sqlRestore.Abort();  // This does not stop the restore state.
        sqlServer.KillAllProcesses(databaseName);  // ??  this does not help
        sqlServer.Refresh();
        connection.Cancel(); 
        connection.Disconnect();
        sqlCon.Close();     
        return;
    }

    try
    {
        db = sqlServer.Databases[databaseName];
        db.SetOnline();      // some examples do not do these two lines. not using seems to cause problems
        sqlServer.Refresh();
    }
    catch (Exception ez)
    {
        connection.Cancel();  // Important
        connection.Disconnect();
        sqlCon.Close();      // 
        return;
    }

    // The Full and Incremental Restores both completed without error and the database is online and operational
    connection.Cancel();  
    connection.Disconnect();
    sqlCon.Close();     
}