Error attaching a database (.mdf file) to SQL Server

Question

I'm having trouble attaching a database DBName.mdf to a network SQL Server. The admin can manually attach the database but if I try, I get the following error message.

Database 'DBName' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery. (Microsoft SQL Server, Error: 3415)

Here is my code:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = @"Server=" + SQLServerName + ";database=master;User ID=" + UserName + ";Pwd=" + Password; ;

try
{
    conn.Open();

    System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand("CREATE DATABASE DBName ON ( FILENAME = '" + @"C:\DBName.mdf" + "' ), ( FILENAME = '" + @"C:\DBName_log.ldf" + "' ) FOR ATTACH", conn);
    com.ExecuteScalar();

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

Here is the details of SQL Server

- Product - Microsoft SQL Server Express Edition (64-bit)
- Version - 11.0.2218.0

I can create a new database just fine but cannot attach an existing database. What am I missing here?

Any help will be appreciated.

Could you try to do it `"CREATE DATABASE DBName ON ( FILENAME = '" + @"C:\DBName.mdf" + "' ), ( FILENAME = '" + @"C:\DBName_log.ldf" + "' ) FOR ATTACH"` manualy? Does it work? — NoWar, Apr 10 '13 at 14:47
It gives the above error if I do it. If the admin of the machine of that SQL Server does it, it works fine. — Butters, Apr 10 '13 at 14:54
So you need the admin credentials to do it. Are you using here `conn.ConnectionString = @"Server=" + SQLServerName + ";database=master;User ID=" + UserName + ";Pwd=" + Password; ;` an ADMIN login? Perhaps you have to give your MS SQL Server user more freedom :) — NoWar, Apr 10 '13 at 14:55
Basically. But why? I can create database on that Server Just fine. Why do I need admin privilege for attaching a database? — Butters, Apr 10 '13 at 14:57
Have a read here http://msdn.microsoft.com/en-us/library/ms190209(v=sql.105).aspx and here http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/bfae0299-b2af-4d31-8a34-887a9c0765f8/ — NoWar, Apr 10 '13 at 14:59
But, the application that runs the above code is run as administrator and still gets the same error. — Butters, Apr 10 '13 at 15:31
You have to use the ADMIN login of MS SQL SERVER. Just ask your DBA to provide you the `sa` login and try it. — NoWar, Apr 10 '13 at 15:37

score 15 · Accepted Answer · answered Apr 11 '13 at 18:45

15

I figured out what the problem was.

This link was helpful in figuring out the problem. http://www.sqlservercentral.com/Forums/FindPost1367859.aspx

The user under which the SQL Service was running didn't have full access permissions to that folder. I gave the user full permission and everything worked fine.

answered Apr 11 '13 at 18:45

Butters

947
5
16
25

1

Excellent solution. After 3 hours of trying to move Enterprise Vault SQL database files I added the SQL Server Service logon account to the destination folder and bingo was able to attach the databases. Thank you – Topper454 Nov 07 '13 at 20:10

pblack · Answer 2 · 2014-11-02T12:59:28.547

7

I had the same error trying to attach via SQL server management studio.
Running SQL server Management studio as Administrator solved this problem for me. Info gleaned from here http://www.nickyvv.com/2013/02/database-databasename-cannot-be.html.

edited Nov 02 '14 at 12:59

answered Nov 02 '14 at 12:53

pblack

778
1
6
13

score 1 · Answer 3 · answered Dec 02 '14 at 13:18

I also faced the similar problems and I resolved it using both approaches listed above by Butters and pblack. To sum up they are:

Go to the system services and find SQL Server Database service.
Right click on the service and select properties
Go to the Log On tab
If the "Log On As" user from your service is a user account, make sure that user account has Full Control on that folder.
If the "Log On As" user from your service is "Network Service" or "Local System" those account should already have access, but go ahead and add them and give them Full Control.
Lastly, RUN SQL Server management studio using Administrative privileges

score 0 · Answer 4 · answered Apr 25 '14 at 06:01

I also faced similar situation while using T-SQL Script but i choose another way through SSMS. Here are some easy steps that are very helpful to you to attach SQL MDF file through SQL Server Management Studio.

Go through Start Button->All Programs->SQL Server XXXX->SQL Server Management Studio
Login in it
Select Object Explorer enlisted databases
Then Right click on it and Select Attach database
Database Attachment Windows appears on the Screen then click on ADD button
Then you need to select MDF file which you want to attach and click OK
Confirm it and Click OK
Database is successfully attached. You can checkout in the Databases list.

Error attaching a database (.mdf file) to SQL Server

4 Answers4

Linked