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.