CODEWITHSUNDEEP
c#sqlsql-serverdatabasedatabase-restore
Masoud Abasian
Masoud Abasian

Reputation: 10919

Problem In Restore Database In C#

I define every thing Already and this code is a part of my code

if (Sql_Conn == null)
    Sql_Conn = new SqlConnection(Str_Con);
// str_con is my connection string

if (Sql_Conn.State != ConnectionState.Open)
   Sql_Conn.Open();

Data_Table = new DataTable();
DataA_dapter = new SqlDataAdapter();

Sql_Cmd = new SqlCommand();
Sql_Cmd.Connection = Sql_Conn; // 

string sql = "RESTORE DATABASE  [" + str_directory + "] From DISK = " +
                        "'" + strFileName + "' ; ";
// str_directory is the source of my database as DB.MDF
// srtFileName is the directory of DB.bak
Sql_Cmd.CommandType = CommandType.Text;
Sql_Cmd.CommandText = sql;

try
{
    Sql_Cmd.ExecuteNonQuery();
}
catch (SqlException Error_Exception)
{
   MessageBox.Show("Error");
}

When I use string sql in SQL Sserver with new query I don't have problem and my database restores successfully but when I use this code with c# I see this error

Error : {System.Data.SqlClient.SqlException: RESTORE cannot process database 'E:/DB.mdf' 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 want to restore my database. I have to open connection at the first of my codes and when I want to restore my database I see Exception.

Now how can I restore my database? Please Help Me.

Upvotes: 2

Views: 4329

Answers (4)

Huske
Huske

Reputation: 9296

The problem is that you connection string is being initialized with Initial Catalog (or Database) set to the database that you want to restore. This causes a connection to be active (you can check this by executing sp_who2 system stored procedure) thus prohibiting you to do restore. You should set your Initial Catalog (or Database) value in a connection string to master database. This should leave your target database free from connections and will allow you to do the restore process.

Upvotes: 2

Emond
Emond

Reputation: 50692

Check the connection string. If it contains an Initial Catalog setting set it to Master.

Upvotes: 1

Tim
Tim

Reputation: 28530

You need to be connected to a different database than the one you are trying to restore. Set a different database (inital catalog) in you connection string, connect, then run the code.

See how to restore sql-server database Through C# code for the same problem.

Upvotes: 3

Guffa
Guffa

Reputation: 700720

You can't restore the exact database that you have connected to. As the error message mentions, you should use the master database instead.

In your connections string use database=master.

Upvotes: 5

Related Questions