CODEWITHSUNDEEP
c#sqlsql-servervisual-studio-2012
waleed-gfx
waleed-gfx

Reputation: 23

Error when restoring SQL Server database from C#

I have a form to restore a database backup, when user select file.bak and clicks on a Restore button, this message shows up:

enter image description here

and my database in SQL Server changes to Single User mode like that

enter image description here

I use Windows authentication

enter image description here

My code

///// declare some var in partial class 
public partial class RestoewDB : Form
{
    private string connectionString = @"server=.\SQLEXPRESS;DataBase=StoreDataBase; Integrated Security=true";
    private SqlConnection conn;
    private SqlCommand command;

    //////////////
    // my code when user select file.bak and click restore button
    /////////////
    try
    {
        string DatabaseName = "StoreDataBase";

        conn = new SqlConnection(connectionString);
        conn.Open();

        string sql = "Alter Database " + DatabaseName + " Set SINGLE_USER WITH ROLLBACK IMMEDIATE; ";
        sql += "Restore Database " + DatabaseName + " FROM Disk ='" + textBox1.Text + "'; ";
        command = new SqlCommand(sql, conn);
        command.ExecuteNonQuery();

        textBox1.Text = "";
        MessageBox.Show("Successfully Restored Database.");

        conn.Close();
        conn.Dispose();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Upvotes: 0

Views: 998

Answers (1)

Michael Petrotta
Michael Petrotta

Reputation: 60962

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

This tells you what you need to know. You cannot perform a restore on a database when you're using it in your session. Use another database - master, as the error message recommends, is a good choice. Change your connection string accordingly.

server = .\SQLEXPRESS; DataBase=master; Integrated Security=true

Upvotes: 6

Related Questions