title: C# SQL ExecuteNonQuery Returns Control Before Query Completes
body: <p>I'm using a C# console application to restore a database nightly.  I have the C# app call a stored procedure to perform the restore using <code>ExecuteNonQuery</code>. Then it runs a second stored procedure on the newly restored database.  Sometimes (not always) that second stored procedure fails with the error:</p>
  <blockquote>
  <p>Database 'dbnamehere' cannot be opened. It is in the middle of a restore.</p>
  </blockquote>
  <p>My understanding is that control shouldn't be passed to the second <code>ExecuteNonQuery</code> before the first <code>ExecuteNonQuery</code> finishes. So, why is this happening? What's the solution for this?</p>
  <p>C#</p>
  <pre><code>command.CommandType = System.Data.CommandType.StoredProcedure;
  command.CommandText = @&quot;dbnamehere.dbo.sprocDailyRestore&quot;;
  command.CommandTimeout = 3600;
  command.ExecuteNonQuery();
  </code></pre>
  <p>SQL</p>
  <pre><code>ALTER DATABASE dbnamehere SET single_user WITH ROLLBACK IMMEDIATE;
  
  RESTORE DATABASE dbnamehere 
  FROM DISK = 'C:\mypathhere\filename.bak' 
  WITH MOVE 'filename' TO 'C:\MSSQL\Data\dbnamehere_Primary_Data.mdf',
       MOVE 'ETLStaging_4D600DAC' TO 'C:\MSSQL\Data\dbnamehere_ETLStaging_Data.mdf',
       MOVE 'RealAnalytics_7AFB9B94' TO 'C:\MSSQL\Data\dbnamehere_Analytics_Data.mdf',
       MOVE 'filename_log' TO 'C:\MSSQL\Data\dbnamehere_Primary_Log.ldf', 
       REPLACE, NORECOVERY;
  
  RESTORE DATABASE dbnamehere 
  FROM DISK = 'C:\scheduled-tasks\RealPageDownloader\Repo\filename.diff' 
  WITH MOVE 'filename' TO 'C:\MSSQL\Data\dbnamehere_Primary_Data.mdf',
       MOVE 'ETLStaging_4D600DAC' TO 'C:\MSSQL\Data\dbnamehere_ETLStaging_Data.mdf',
       MOVE 'RealAnalytics_7AFB9B94' TO 'C:\MSSQL\Data\dbnamehere_Analytics_Data.mdf',
       MOVE 'filename_log' TO 'C:\MSSQL\Data\dbnamehere_Primary_Log.ldf',
       REPLACE;
  
  
  ALTER DATABASE dbnamehere SET multi_user;
  </code></pre>
  <p>I should note both stored procedures are executed using <code>ExecuteNonQuery</code> and both stored procedures live in a different database than the one being restored.</p>