How to restore SQL Server 2012 database?

Question

How to restore a database from SQL Server 2012?

This is my code:

private void [connTes()][1]
{
        try
        {
            conString = "server=.\\SQLEXPRESS;database=db_datatestproject;user=admin;password=123;Integrated Security=True";
            connnn = new SqlConnection(conString);
            connnn.Open();
        }
        catch
        {
        }
}

private void button1_Click(object sender, EventArgs e)
{
        connTes();

        try
        {
            if (txtlocation.Text == "")
            {
                MessageBox.Show("select database");
                return;
            }
            else
            {
                string databesing = connnn.Database.ToString();
                string a = "ALTER DATABASE " + databesing + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
                a += "RESTORE DATABASE "+databesing+" FROM DISK ='"+txtlocation.Text+"' WITH REPLACE;";

                SqlCommand cmd = new SqlCommand(a, connnn);
                SqlDataReader dr = cmd.ExecuteReader();
                connnn.Close();
                connnn.Dispose();

                MessageBox.Show("done restored");
            }
        }
        catch(SqlException ex)
        {
            MessageBox.Show(ex.ToString());
        }
}

This is the error I get:

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

How to fix this error?

Any help is much appreciated.

Thank you.

Since you're not getting back any data from that SqlCommand, use ExecuteNonQuery() instead of ExecuteReader().... — marc_s
I meet this issue a lot. How different between cmd.ExecuteNonQuery() and declare SqlDataReader dr = cmd.ExecuteReader()? How Can I see the different? — Sai

Mitch Wheat Mitch Wheat · Accepted Answer · 2018-05-25T04:48:45

Add

USE MASTER; 
GO

to the start of your TSQL restore command.

You need to be in another database and not the one you are restoring.

Also please note @marc_s's comment: "Since you're not getting back any data from that SqlCommand, use ExecuteNonQuery() instead of ExecuteReader()"

How to restore SQL Server 2012 database?

1 Answers