C# SQL ExecuteNonQuery Returns Control Before Query Completes

Viewed 73

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 ExecuteNonQuery. Then it runs a second stored procedure on the newly restored database. Sometimes (not always) that second stored procedure fails with the error:

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

My understanding is that control shouldn't be passed to the second ExecuteNonQuery before the first ExecuteNonQuery finishes. So, why is this happening? What's the solution for this?

C#

command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = @"dbnamehere.dbo.sprocDailyRestore";
command.CommandTimeout = 3600;
command.ExecuteNonQuery();

SQL

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;

I should note both stored procedures are executed using ExecuteNonQuery and both stored procedures live in a different database than the one being restored.

0 Answers
Related