SMO Restore not blocking until complete. I am not using Async

Question

I am using the SMO sdk to restore a database. The sqlRestore method does not block until the restore completes. I have tested this by trying to access the restoring database directly after restoring and I am getting an error of the database is restoring. I have tried calling the wait function.

My code is as below:

        Server sqlServer = new Server("reddsql01");

        Restore dbRestore = new Restore();
        dbRestore.Devices.AddDevice(@"Z:\Test Folder\database.bak", DeviceType.File);
        dbRestore.Database = "RestoredDatabase";
        dbRestore.NoRecovery = true;


        foreach (DataRow dRow in dbRestore.ReadFileList(sqlServer).Rows)
        {
            string logicalFileName = dRow["LogicalName"] as string;
            string physicalFileName = dRow["PhysicalName"] as string;

            dbRestore.RelocateFiles.Add(new RelocateFile(logicalFileName, (Path.GetExtension(physicalFileName) == ".mdf" ? @"D:\\Data\" : @"L:\\Log\") + dbRestore.Database));
        }
        dbRestore.ReplaceDatabase = true;


        dbRestore.SqlRestore(sqlServer);
        dbRestore.Wait();

        Database restoredDB = sqlServer.Databases[dbRestore.Database];
        for (int i = 0; i < restoredDB.Tables.Count; i++)
        {
            Console.WriteLine(restoredDB.Tables[i].Name);
        }

        Console.ReadLine();

I then get the following exception:

Database 'RestoredDatabase' cannot be opened. It is in the middle of a restore.

Can someone please help. I could write something crude to loop around checking if the database has finished restoring but would much prefer a neat solution.

I have also added event handlers to the complete and percent complete and run async. It does mark the database as 100% restored.

Full exception:

    Microsoft.SqlServer.Management.Common.ExecutionFailureException was unhandled
  HResult=-2146233087
  Message=An exception occurred while executing a Transact-SQL statement or batch.
  Source=Microsoft.SqlServer.ConnectionInfo
  StackTrace:
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(String query)
       at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
       at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
       at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
       at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
       at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelDescription, ScriptingPreferences sp)
       at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh)
       at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection()
       at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.get_Count()
       at ConsoleApplication10.Program.Main(String[] args) in c:\Users\Darren.West\Documents\Visual Studio 2013\Projects\ConsoleApplication10\ConsoleApplication10\Program.cs:line 57
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=Database 'RestoredDatabase' cannot be opened. It is in the middle of a restore.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=14
       LineNumber=1
       Number=927
       Procedure=""
       Server=reddsql01
       State=2
       StackTrace:
            at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
            at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       InnerException: 

Database 'RestoredDatabase' cannot be opened. It is in the middle of a restore.

Answer 1

You are setting NoRecovery = true. That's why the database never leaves the restoring state and can't be read.

If you want to peek inside use the standby restore mode.