Restoring a database with ASP.NET MVC using C#-CodePudding

I have been working on implementing a stored procedure to restore a database from a .bak file in an ASP.NET MVC application written in C#.

However, when trying to execute the restore stored procedure, I get this error:

RESTORE cannot process database 'SkyExams' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally.

I have tried various different solutions but I can't seem to figure it out. I've tried using the master database to create the stored procedure, but that didn't work. I've also tried the "set single-user" method (which is commented out in my stored procedure shown below) but no luck with that.

It is for an assignment and I noticed that it mentions that the system may exit during the restore, which I understand would potentially help.

My restore stored procedure:

CREATE PROCEDURE skyExamsRestore
    @name VARCHAR(MAX) = 'SkyExams' -- DB NAME TO Restore
AS
BEGIN
    --ALTER DATABASE SkyExams
    --SET SINGLE_USER
    --WITH ROLLBACK IMMEDIATE

    DECLARE @SQL VARCHAR(MAX)
    DECLARE @NewDBName VARCHAR(MAX) -- NEW DB NAME TO RESTORE THE BACKED UP DB.
    DECLARE @FileNameOrgBackup VARCHAR(MAX) -- Backup file name to restore
    DECLARE @PathOrgignalBackup VARCHAR(MAX) -- Path of backup file to restore

    SET @NewDBName = @name
    SET @PathOrgignalBackup = 'F:\BackupSkyExams\SkyExams_20220930.BAK'
    SET @FileNameOrgBackup = @PathOrgignalBackup   @name   '.bak'

    SET @SQL = 'RESTORE DATABASE '   @NewDBName
    SET @SQL = @SQL   ' FROM DISK = '''   @FileNameOrgBackup   ''''

    EXECUTE(@SQL)

    --ALTER DATABASE SkyExams SET MULTI_USER
END

My button in the view (only put fullstops here so that the tags display properly):

<button type="button" id="btnRestore"  style="width:250px;" onclick="location.href='@Url.Action("BtnRestore_Click", "Sys_User", new { loggedId = Model.SysUser_ID })'">
    Restore database
</button> 

<label>
    @restoreMessage
</label>
<br />
<br />

My action method in the controller:

public ActionResult BtnRestore_Click(int loggedId)
{                
    var obj = db.Database.ExecuteSqlCommand (TransactionalBehavior.DoNotEnsureTransaction, "skyExamsRestore");

    if (obj != 0)
    {
         string temp = "Restore successfully executed.";
         return RedirectToAction("viewAccount", new { id = loggedId, restoreMessage = temp });
    }
    else
    {
        return RedirectToAction("loginScreen");
    }
}

CodePudding user response：

You probably want to create a second connection string in your application that uses the master database rather than logging into the SkyExams DB. Then use that separate connection to run your procedure. The connection string should contain this: Database=master;

Note the account that is being used will need access to the master database. It's probably not a great security thing to have all your database connections have this access right. As such you may want to create a second user in your database for this.

It might also be possible to run a USE master; command from the existing connection before running the procedure.

The procedure itself will likely have to be defined in a database that is separate from the DB it is restoring.