SMO Restore unable to complete restore

Question

I am trying to write an application that will backup and restore a SQL Server Express 2008 database.  I am using SMO to do this task.  The backup seems to work fine, but when I try and restore from it using the SMO Restore oblect the restore seems to complete, but really does not.  By this I mean the completed event fires properly and no visible exceptions have been thrown.  When I open the SQL Server Management Studio, the database state shows as "Restoring..." and the database itself is unavailable.  If I try to bring the database online programmatically following the restore, an exception is thrown indicating that "ALTER DATABASE is not permitted while a database is in the Restoring state ALTER DATABASE statement failed".  I am using Microsoft Visual Studio 2008 SP1 and Microsoft SQL Server Express 2008 for this project.  The following is the code that I am using to try and do this:
  
        public  bool RestoreSMO(string filename)
        {
            try
            {
                Microsoft.SqlServer.Management.Common.ServerConnection conn =
                    new Microsoft.SqlServer.Management.Common.ServerConnection();
                conn.ServerInstance = this.DatabaseName;
                conn.LoginSecure = false;
                conn.Login = this.UserName;
                conn.Password = this.Password;
                conn.DatabaseName = "master"; // You cannot restore a database that you are connected to
               
               
                Microsoft.SqlServer.Management.Smo.Server svr = new Server(conn);

                svr.LoginMode = ServerLoginMode.Mixed;
               
                Database currentDb = svr.Databases[this.Database];
                if (currentDb != null)
                {
                    svr.KillAllProcesses(this.Database);
                }
                Restore restoreDB = new Restore();
                restoreDB.Database = this.Database;
                /* Specify whether you want to restore database, files or log */
                restoreDB.Action = RestoreActionType.Database;
                restoreDB.Restart = true;
               
                restoreDB.Devices.AddDevice(filename, DeviceType.File);

                restoreDB.ReplaceDatabase = true;

                restoreDB.NoRecovery = true;

                // * Wiring up events for progress monitoring
                restoreDB.PercentComplete += new PercentCompleteEventHandler(restoreDB_PercentComplete);
                restoreDB.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(restoreDB_Complete);
                
                restoreDB.SqlRestore(svr);
                
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }

        }

Answer 1

Hello,

Your issue is in the following:

restoreDB.NoRecovery = true;

The database is in fact restored, but in a state that wil accept further log file restores (hence the "restoring..." status in ssms) etc.

Set this value to false and you will be able to access the db post restore.

Cheers,
Rob

Answer 2

That worked!  Thanks :-)