| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2007-10-12 : 11:28:16
|
| Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.What is the likely cause of this?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:32:11
|
| You sure it wasn't read-only when you detatched it?ALTER DATABASE MyDatabaseName SET READ_WRITEAdd WITH ROLLBACK IMMEDIATE if the DB is in use and its doesn't let you.Kristen |
 |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 11:51:51
|
| Yes, I'm sure, and to verify I just reattached it from the USB drive to the origional SQLExpress instance and it comes in just fine. When I attach it to the other instance it comes in ReadOnly.I guess I should try backup and restore instead? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 13:06:36
|
| I'd try setting it to READ_WRITE firstKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 13:33:06
|
| It probably has to do with the USB drive. Perhaps the file is set to read-only on the USB drive and thus read-only when you copy to the server, and then perhaps SQL Server sees this file flag and then sets the database to that setting too.I've never done it this way, so there's a lot of "perhaps" here.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 15:31:32
|
| I checked that also, in fact it was the first thing I checked. Nope, the Read-Only flag is not checked on the file itself, and I also copied the file from the USB drive back to the drive on the source comptuer and re-attached it and it worked fine....I'll try setting it to READ_WRITE, but I suspect that this won't work because I already tried that via the Properties UI and that didn't work. Still sometimes thing work via script that don't work via UI, so I'll try it via script. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 17:58:59
|
| "I suspect that this won't work because I already tried that via the Properties UI and that didn't work."They are going to do the same job, so I'll put my money with your hunch!However, this is NOT normal behaviour.A restored database takes on all then characteristics of the database it was backed up from. If it was Read/Write when it was backed up that's what it will be after restore (or attach).Is it possible that SQL Server has only READ ONLY permissions on the file? (e.g. the folder it has been copied into has different security profiles to the source machine?)Kristen |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 19:47:54
|
| Well, for whatever reason, doing a backup and restore worked fine, but simply reattaching the mdf still didn't work.It seems to me that I should have been able to simply detach the database, make a copy of it, and then attach the copy, but that didn't work.Making a backup and then copying the backup and restoring the backup on the new server worked however.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 21:35:17
|
| That is very interesting. I wonder if there are any limitations in Express with regards to detach/attach. I only use Enterprise Edition of SQL Server, so I'm not familiar with the different limitations of the lower edition products.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-13 : 10:22:57
|
| Doing a "restore" is creating a new file (or using an existing one) on the target machine, as such it will inherently have appropriate file level permissions - even if, for whatever reason, the Backup File being restored from only has Read permissions.If you have a moment might be worth checking what the security permissions were on the MDF file you copied across. I reckon SQL Server will have only had Read permission [not the same as the file being Read-Only, but if the application attempted to Write to the file that would have been denied, which would look like it was Read Only I suppose]Kristen |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-13 : 19:14:28
|
| That's not it. As I said, I can detach it and attach it as much as I want on the same server, it comes in normal every time. When I copy it to the other server and attach it, then it comes in Read Only. The database image in the SSMSEE tree is actually gray and the database says (Read Only) next to the name in the tree.I'm not going to fool with it anymore. I don't know what the issue is, but doing a backup and restore does what I need in this case. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 01:04:12
|
| "I don't know what the issue is, but doing a backup and restore does what I need in this case."Yup, agree with that, I was just trying to get to the bottom of it for the next unfortunate soul who Google's this!"When I copy it to the other server and attach it, then it comes in Read Only."Not sure I explained what I meant clearly.When you copy that file it will take the file permissions [from the Source machine] with it. On the Target Server those security setting may not provide WRITE permission to the "windows logon" that the Target server's SQL Service is using when it tries to Attach the fileAnyways, you've got a working solution, and my preference would be for Backup / Restore anyway.Kristen |
|
|
|
josjorge
Starting Member
2 Posts |
Posted - 2008-06-29 : 09:56:55
|
Hi,I've got the same problem when i detached a database from my SQL at home and tried to attach it to my office SQL. I fixed it doing this.In the admin tools (Control Panel)go to services , open the one for SQL Server(SQLEXPRESS) ,go to log on tab ,change the login credentials to Local System Account, restart the service and then try to attach the database again. It worked for meJosequote:
Originally posted by malachi151
Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.What is the likely cause of this?Thanks
|
|
|
|
eagle
Starting Member
1 Post |
Posted - 2008-11-26 : 04:16:49
|
| I had the same problem when I first did a silent installation with sqlexpr.exe, and did not specify sqlaccount="NT AUTHORITY\SYSTEM", on a Windows XP SP 2 and SP 3. The result was the database I "silently" attached, became a read-only, contrary to what I needed. The default was Network Service.After installation of the SQL Express, I changed to Local System. Then by executing "alter database mydbname set read_write", it became read-write access, thus solving my read-only problem. |
|
|
|
ibeckett
Starting Member
12 Posts |
Posted - 2009-11-18 : 11:49:06
|
| Hey I know this is an old thread, but I had a similar issue that turned this up.After copying the DB's from an USB/eSATA drive to a local hard drive, we had the same problem as the thread starter - where the DB's were attaching as read only. The fix was quick and simple: Go to the DB file on the local hard drive and fix the permissions so that the local admin had full access on the file.Thanks,Ian Beckettibeckett at gmail dot comwww.sqlblog.ibeckett.com |
|
|
|
ITM_2005
Starting Member
1 Post |
Posted - 2010-09-30 : 05:02:25
|
This worked perfectly for me.quote:
Originally posted by josjorge
Hi,I've got the same problem when i detached a database from my SQL at home and tried to attach it to my office SQL. I fixed it doing this.In the admin tools (Control Panel)go to services , open the one for SQL Server(SQLEXPRESS) ,go to log on tab ,change the login credentials to Local System Account, restart the service and then try to attach the database again. It worked for meJosequote:
Originally posted by malachi151
Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.What is the likely cause of this?Thanks
|
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2010-10-26 : 12:14:47
|
| Adding my experienced to this thread. I had this same issue also. On the same server I detached the database, moved the data file to a different drive, and reattached. It came back in read only mode. I executed an alter database to set it to read_write and the operation went into a spinloop. The status would sometimes change to running for a few seconds but it was stuck in a spinloop. The sql server generated a stack dump and was putting non-yielding scheduler warnings in the error log. When i tried to kill the connection it then was stuck in killed/rollback status. The connection then started to block some of the other system SPIDs, which in turn started blocking other user connections to different databases. I had to do an emergency restart of the service, but when it would start back up it would immediatly stack dump again when it tried to run recovery to this database that i just attached and was stuck in read only. I started the server with the trace flag to skip the recovery phase for all databases, hoping i could then just drop the bad database, and restart it normally, but when I executed the drop database it stack dumped again. At this point I needed to move quick. I simply deleted the data file for the bad database from the disk, and started the server. Everything game up fine. The bad database was still in read only mode, even though its data files were completely gone. I was finally able to drop the database successfully and then restore it from backup. This was a very critical outage for my company. Fortunately i was able to minimize the outage to only a few minutes.As to the cause, I do not know for sure if it was file system permissions. I granted the local sql server group full access to the folder on this new drive that was holding the data file. The local admin group should have had access because they have full control over the root drive. I did have to change the owner of the file from the service account we are running sql server as to the local admin group to move it out of its old spot. This may have been the cause of it getting attached in read-only mode. After doing the restore, since sql server itself created the file, the account we have running the service is the owner of the file currently.Moral of the story. Be extremely careful with file system permissions when using detach/attach to move a data file around.- Eric |
|
|
|
mount77
Starting Member
1 Post |
Posted - 2011-04-14 : 18:04:09
|
| After attaching a database that I copied from another machine I, too, was confronted with a Read-Only database. I received an Access Denied error on the mdf file when I tried to set the Database Read-Only property to False. To solve the problem I granted Full Control on both the mdf and log files to the account under which the SQL Server service runs. Then I could set the property to False. |
|
|
|
rgonv
Starting Member
1 Post |
Posted - 2012-01-11 : 18:44:05
|
| I had a similar problem, and it seems related to file level permissions. When I attached a db using one particular admin user, the db worked ok,but using a different (also administrative) login, went into readonly mode. Managed to explicitly asign full file level permissions to the second login and worked perfectly.Hope it helps.Cheers,http://about.me/ricardo.gonzalez |
|
|
|
|
Database is ReadOnly when I attach it...