Copying MDF causes it to become read-only

Question

Copying MDF causes it to become read-only

chrisph

Hall of Fame

Points: 3701
More actions
January 20, 2011 at 10:10 am

#245711

Hi all,
I encountered a strange issue. I have an MDF file that I detached and want to attach on a server outside of our LAN. When I copied the file (via usb stick, FTP) and then try to attach it I get an error that the main file is read-only. The work-around was using backup/restore.
Any ideas why the file becomes read-only? It does not set the flag in file properties/NTFS permissions, something is happening on the database level.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Does SQL have full control over the folder (or just read)? Compressed folder?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chrisph Hall of Fame Points: 3701 More actions · Answer 2

Gial, how would I know if SQL has full control over a folder? NTFS permissions state that I have admin privileges for the same windows domain account which is dbo of all databases on the server. So I assume yes.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Your permissions have nothing to do with anything. It's the permissions of the account that SQL runs under that's important.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 738673 More actions · Answer 4

Check under Configuration Manager for the instance and see which account SQL Server runs under. Then check the permissions for that account on that folder.

chrisph Hall of Fame Points: 3701 More actions · Answer 5

I checked and the instance is running under the "Network Service" account. This server is on the DMZ not on the domain so I can't use a domain account. Thoughts on that?

Steve Jones - SSC Editor SSC Guru Points: 738673 More actions · Answer 6

Add the permissions for full control to the Network Service account for that file or folder. Perhaps they are not there.

chrisph Hall of Fame Points: 3701 More actions · Answer 7

chrisph

Hall of Fame

Points: 3701

January 20, 2011 at 1:46 pm

#1276244

Thank you Steve, that resolved my issue 🙂

Leo.Miller SSChampion Points: 13051 More actions · Answer 8

By the way this is an issue on SQL 2005. If you detach a file and copy/move it to a new location you lose permission on the file. For this reason I've been taking the database off line to move the files as this doesn't cause the same problem.

Use this:

ALTER DATABASE MyDatabase

SET OFFLINE;

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME='MyDatabase', FILENAME='H:\Data\MSSQL.3\MSSQL\Data\MyDatabase.mdf');

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME='MyDatabase_Log', FILENAME='H:\Data\MSSQL.3\MSSQL\Data\MyDatabase_log.ldf');

GO

ALTER DATABASE MyDatabase

SET ONLINE;

GO

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.