[Solved] ASP.Net restore database Error: Exclusive access could not be obtained because the database is in use

Hi suhaas121,

Someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. So you need to use ROLLBACK before restore.

Refer below code.

C#

try
{
    string backupfile = Server.MapPath("~/Revision/Navy/") + ListBox1.SelectedItem.Text;
    String query = "USE master GO" + Environment.NewLine;
    query += "ALTER DATABASE IETM_Kanpur SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO";
    query += "RESTORE DATABASE IETM_Kanpur FROM DISK='" + backupfile + "' GO";
    String conn = "Data Source=DESKTOP-T5BCC5O; Initial Catalog=master; Integrated Security=true";
    SqlConnection con = new SqlConnection(conn);
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = query;
    cmd.Connection = con;
    cmd.ExecuteNonQuery();
    con.Close();
    Label1.Text = "Database Has Been Restored Successfully";
}
catch (Exception ex)
{
    Label1.Text = "Error Occured While Creating Backup of Database Error Code" + ex.ToString();
}

VB.Net

Try
    Dim backupfile As String = Server.MapPath("~/Revision/Navy/") + ListBox1.SelectedItem.Text
    Dim query As String = "USE master GO" & Environment.NewLine
    query += "ALTER DATABASE IETM_Kanpur SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO"
    query += "RESTORE DATABASE IETM_Kanpur FROM DISK='" & backupfile & "' GO"
    Dim conn As String = "Data Source=DESKTOP-T5BCC5O; Initial Catalog=master; Integrated Security=true"
    Dim con As SqlConnection = New SqlConnection(conn)
    con.Open()
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.CommandText = query
    cmd.Connection = con
    cmd.ExecuteNonQuery()
    con.Close()
    Label1.Text = "Database Has Been Restored Successfully"
Catch ex As Exception
    Label1.Text = "Error Occured While Creating Backup of Database Error Code" & ex.ToString()
End Try