Problem In Restore Database In C#

Question

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.

possible duplicate of How to restore SQL Server database through C# code — marc_s
– marc_s, Commented Aug 6, 2011 at 18:25

Guffa · Accepted Answer · 2011-08-06 18:04:07Z

5

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.

answered Aug 6, 2011 at 18:04

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:26:53Z

3

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.

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Aug 6, 2011 at 18:05

Tim

28.6k8 gold badges65 silver badges80 bronze badges

Comments

Huske · Accepted Answer · 2011-08-06 18:06:29Z

2

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.

answered Aug 6, 2011 at 18:06

Huske

9,3062 gold badges40 silver badges55 bronze badges

2 Comments

Masoud Abasian Over a year ago

Thank you . but how can i open master database and what is connection string for this database. i do this but i see this error : >An attempt to attach an auto-named database for file master.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share

Huske Over a year ago

You connection string should be something like this <add name="MyCS" connectionString="Data Source=YOUR_SERVER;Initial Catalog=master;Integrated Security=True;" (or if you are using SQL Server authentication replace Integrated Security=True with User ID=your_user_id;Password=your_password;" />

Emond · Accepted Answer · 2011-08-06 18:05:40Z

1

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

answered Aug 6, 2011 at 18:05

Emond

50.8k11 gold badges86 silver badges118 bronze badges

2 Comments

Masoud Abasian Over a year ago

Thank you . but how can i open master database and what is connection string for this database. i do this but i see this error : >An attempt to attach an auto-named database for file master.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share

Emond Over a year ago

@Masoud Abasian, could you show your connection string (without password!)?

Collectives™ on Stack Overflow

Problem In Restore Database In C#

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related