CODEWITHSUNDEEP
c#sql-serverrestoreaccess-denied
Ali_dotNet
Ali_dotNet

Reputation: 3279

access denied error while connecting to newly restored MDF database

I'm restoring a SQL Server .bak file on SQL Server Express. But after restoring the database, I cannot use it in my code, it seems that the file is somehow locked, and the only way is copying it to another folder, also when I try to copy the .mdf file (using Windows Explorer) I get a warning about admin permission. I cannot copy this file using C# File.Copy (unauthorizedaccessexception, access denied), here is my code:

SqlConnection myConn = new SqlConnection("Server=" + sqlname + ";Integrated security=SSPI;database=master");
string dbname = "tmpDB" + DateTime.Now.Ticks.ToString();
str = "CREATE DATABASE " + dbname + " ON PRIMARY " +
            "(NAME = MyDatabase_Data, " +
            "FILENAME = '" + System.IO.Path.GetDirectoryName(openDialogConvert.FileName) + "\\" + dbname + ".mdf') " +
            "LOG ON (NAME = MyDatabase_Log, " +
            "FILENAME = '" + System.IO.Path.GetDirectoryName(openDialogConvert.FileName) + "\\" + dbname + ".ldf') ";

SqlCommand myCommand = new SqlCommand(str, myConn);

try
{
   myConn.Open();
   myCommand.ExecuteNonQuery();
}
catch (System.Exception ex)
{
   MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

myCommand.Dispose();

str = @"RESTORE DATABASE [" + dbname + "] FROM  DISK = N'" + openDialogConvert.FileName + @"' WITH  FILE = 1,  MOVE N'IODB_Data' 
TO N'" + System.IO.Path.GetDirectoryName(openDialogConvert.FileName) + "\\" + dbname + @".mdf',  MOVE N'IODB_Log' 
TO N'" + System.IO.Path.GetDirectoryName(openDialogConvert.FileName) + "\\" + dbname + @".ldf',  REPLACE ";
myCommand = new SqlCommand(str, myConn);
myCommand.ExecuteNonQuery();
myCommand.Dispose();

myConn.Close();

//here I'm going to connect to my newly created & restored database, but I get access denied error
SqlConnection sql = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + System.IO.Path.GetDirectoryName(openDialogConvert.FileName) + @"\" + dbname + ".mdf  ;Integrated Security=True");
sql.Open();

What is going wrong here? I want to connect to my newly restored database as soon as I restore my database.

I get following error when I try to connect to my newly created & restored .mdf:

Unable to open the physical file "D:\9 mordad fara\Ofogh-Dsk\Ofogh-Dsk\bin\Debug\tmpDB635110451805001328.mdf". Operating system error 5: "5(Access is denied.)".

An attempt to attach an auto-named database for file D:\9 mordad fara\Ofogh-Dsk\Ofogh-Dsk\bin\Debug\tmpDB635110451805001328.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Upvotes: 0

Views: 1041

Answers (1)

Jonathan
Jonathan

Reputation: 26629

I suspect it may be already attached.

Can you connect using:

new SqlConnection("Server=" + sqlname + 
                  ";Integrated security=SSPI;" + 
                  "database=" + dbname);

Upvotes: 2

Related Questions