SQL Server Upgrade 2000 - 2005 files read only but not really

Question

Hi,

I'm trying to upgrade from SQL Server 2000 to 2005. The problem I am having is that when I try to attach the existing db files I get a message that says "database cannot be upgraded because it is read only or has read only files...."

Thing is... there is no write protection on the files.

Can anyone advise me on how to overcome this problem so that I can attach the db, please?

 

Thank you

Robert

Answer 1

The error number that I get is 3415

Answer 2

Hi Rib,

i have the same problem while attaching old sql 7 db's on a sql2005 server. allways the error 3415 appears but none of the DB files is write protected. do you have a current solution for this problem? if yes can you be so kind to give me a hint to solve the problem.

thanks

Michael

Answer 3

Hi,

 

meanwhile i hvae found a solution for the problem attaching a DB. I run the SQL2005 database module service onn the local systemaccount NT AUTHORITY\NETWORKSERVICE. This user is not in the user list for the director where the .mdf file is placed. After adding tis user with full access to the user list of the directory including all subdirectories everthing works fine.

perhaps anyone can use this to solve equal problems.

 

Michael

Answer 4

I had the exact same problem, and I also installed SQL2005 with the NETWORK SERVICE user (the default installation choice). Your solution works perfectly!

Answer 5

Hi!

 

I've this problem too and I've noticed that the problem is caused by user used to logon to service. When we install, normally we do as LOCAL SERVICE. The user for it is configured to NT AUTHORITY\NETWORKSERVICE. This user doesn't have rights to attach DB.

 

Solution:

1) Open Tools Admin (into Start Menu of Windows) and run Services. Look for service named "SQL SERVER (SQL EXPRESS)" and stop it.

2) Double-click it and go to LOGON flap

3) You'll se clicked "THIS ACCOUNT" pointing to that user. Click onto SYSTEM ACCOUNT, then OK

4) Star this service again

 

Ready! Now you can attach the DB

;-)

Answer 6

Hello,
What is the best approach for attaching a database during a setup ? Where should I copy the files so the account has write rights ?
Please help!
Thanks

Answer 7

Hello Dear,

    I was facing same problem. I just change the location of data base. I put my DB on following location

                            C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

   and it work.

Answer 8

pawan jai, just wanted to post and say thanks for taking the time to put your solution up. I was having this issue but moving the files in the SQL data folder in Program Files fixed it for me. You save me a lot of time, much appreciated!

My issue is not really the same as the title of this thread. I had only data and log files from SQL 2005 and was putting onto SQL 2008... this may have also worked for going from 2005 to 2005 or 2008 to 2008... not sure.

FYI here's the attach code I used:

EXEC sp_attach_db @dbname = N'DatabaseName',

@filename1 = N'C:\Program Files\Microsoft SQL Server 2008\MSSQL10.SQL2008\MSSQL\DATA\DataFileName.mdf',

@filename2 = N'C:\Program Files\Microsoft SQL Server 2008\MSSQL10.SQL2008\MSSQL\DATA\LogFileName.ldf'

Note that this creates a new database, so the database named DatabaseName should not already exist when you run it.

Cheers,

Tyler

---

-- Tyler Mitton http://www.rippleaudio.com __________________________________ Need Background Music? Buy & Sell Audio @ http://www.rippleaudio.com

Answer 9

Hi!

 

I've this problem too and I've noticed that the problem is caused by user used to logon to service. When we install, normally we do as LOCAL SERVICE. The user for it is configured to NT AUTHORITY\NETWORKSERVICE. This user doesn't have rights to attach DB.

 

Solution:

1) Open Tools Admin (into Start Menu of Windows) and run Services. Look for service named "SQL SERVER (SQL EXPRESS)" and stop it.

2) Double-click it and go to LOGON flap

3) You'll se clicked "THIS ACCOUNT" pointing to that user. Click onto SYSTEM ACCOUNT, then OK

4) Star this service again

 

Ready! Now you can attach the DB

;-)

hi, your answer works the best and most comfortable. THANKS!!

Answer 10

Granted that this topic is old, but I found this while searching the net, so others may too. 

SQL 2005/2008 controls access to the physical files using NTFS ACLs.  So if you try to attach a DB in a directory other than the default data directory, you must make sure that the account under which SQL runs has FULL CONTROL.  Full control is required, rather than just write, because the engine sets ACLs on the files directly.  Further, I noticed that the Read-Only error above ocurrs when trying to attach a DB while logged into SSMS with a trusted account if that trusted account does not have write access to the folder on the server where the attach is occurring.  I'm not sure why that would be necessary, but it could be that Kerberos delegation is enabled in my configuration. 

So, if you are sure that the SQL Server service startup account has Full Control to wherever the DB and Log files are, the next thing to check is that the current logged on account also has write access to the server's folder where the DB files are located.  Strange it is, but that one change made the attach suddenly start working.

Answer 11

It' works. Thank

Answer 12

Fantastic, it works, thank you

Answer 13

Fantastic, it works, thank you[2]

Answer 14

excellent, it worked for my.

 

i had a similar problem attaching a database in Windows 7.

 

thanks.

Answer 15

I was able to get this to work simply by running the SQL Server Management Console as Administrator.

---

- Mark Z.

Answer 16

Thanks Michael

 

Also if you are trying to attach a database on a new server with a non standard user running SQL service this happen.

Error message:
Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. (Microsoft SQL Server, Error: 3415

 

Solution:

1. Look in services and see what account is the logon account for SQL Server (MSSSQL Server).

2. give the service account permissions to write to the folder where the database file is.

 

//JimiSweden

 

Answer 17

Hello Dear,

    I was facing same problem. I just change the location of data base. I put my DB on following location

                            C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

   and it work.

Thanks so much. you saved me

Answer 18

Similar solution for me.

I moved a DB from a SQL2005 to a SQL2008 Server . I created a new folder and dropped the MDF and LDF in it. That error came up.

So...I moved it to the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA folder which is the default for SQL2008.

After the move, I attached with no problem.

Answer 19

I THINK THIS IS A BETTER FIX:

I had the same issue on SQL Server 2008 R2 today - turns out you don't need to mess with permissions on the files. It's caused by the wonderful UAC crap in Server 2008 R2 - I launched the SSMS as administrator (right-click - warning etc) and it worked perfectly. Thanks Microsoft for misleading us with this article that comes up first in Google search: http://support.microsoft.com/kb/931640  

Answer 20

X_Klamer - Thank you. You have provided the best and simple answer.

Answer 21

Hi,

Thanks for your information.....my problem got resolved really thaks yar

Answer 22

Same here.

Answer 23

I tried most of the suggestions; nothing seemed to work.  finally I read Michael's fix (launch SSMS as Administrator) and it worked on the first try.  Thanks for the help!